run query from file

[JennyPeters]

Does anyone know if it's possible to run a query from a file? I'm trying to update a client database and don't want them to have to zip and send me the db. I'd rather send them a file that they run to make the update. It's a simple data change.

Thanks,
Jenny

 

[PWise]

If what you want to do is change values in a table what you should do is mimic their file structure of your computer and create a mdb with a linked table and create a update query and have them run the update query

 

[CautionMP]

JennyPeters,
If by data change you mean some type of action query you could do something like this:
[ol][li]Create a blank database to send to the client.[/li]
[li]Create a new form.[/li]
[li]Add a textbox ([tt]txtOtherDB[/tt]) that will hold the path the client's database (I leave it up to you how to get the correct path into the textbox).[/li]
[li]Add a command button ([tt]Command1[/tt]) with a variation of the following code for the on click event:

Private Sub Command1_Click()
Const sqlUpdate As String = "[b]UPDATE [i]YourTable[/i] SET [i]YourField[/i] = [i]YourValue[/i];[/b]"
Dim conUpdate As New ADODB.Connection '[b]*[/b]
With conUpdate
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Open [b]Me.txtOtherDB[/b]
  .Execute Me.txtSQL
End With
conUpdate.Close
Set conUpdate = Nothing
End Sub

[small]* You will need to make sure that your VBA project has a reference to one of the Microsoft ActiveX Data Object libraries.
[/small][/li]
[li]Save the form.[/li]
[li]Test the form and maybe add a little error handling to the routine.[/li]
[li]Use this form as the startup form for the database.[/li][/ol]

[tt]sqlUpdate[/tt] should accept any valid SQL commands so you can add/update/delete records. Add/Remove tables, fields, contstraints, primary/secondary keys...

If everything works correctly you should be able to send the new database to your client, they can open and select the path & filename of the database that needs the update, click the command button, and the update you send them should happen.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[CautionMP]

oops!

...
  .Execute sqlUpdate '[red][s]Me.txtSQL[/s][/red]
...

CMP