Button to run Update Query in Diff Database

[Juan4412]

I want to put a button on a form in DB1, and when the button is pressed, I want to update field1 in DB2 to "Richard." What VB code would I need in order to make this happen?

[TheShabz]

You have 2 options.
First, you could write out a connection string to the second database. I'm not the best with connection strings so Google it or wait for someone to reply.
Second, you could simply link to the table via the linked table manager and then treat it as if it was a local table.

[Juan4412]

I would rather follow the connection string route, would less clutter a db I am trying to keep clean. Can someone point me in a direction on where to start, with writing some VBA to perform this?

I think the code would start with something like this, I just don't know what to put in on the If...Then portion....

Private Sub Update()
Dim IsFormLoaded As Boolean
Dim AccessDB As DAO.Database

IsFormLoaded = False

If ....... Then

End If

[TheShabz]

Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase("FullPathAndFileNameOfThe2ndDb")
db.Execute "TheQueryNameYouWishToExecute", dbFailOnError
Set db = Nothing

http://www.devhut.net/2010/11/08/ms-...ther-database/

[TheShabz]

[Juan4412]

I am unfamiliar with this:

Set db = DBEngine.Workspaces(0).OpenDatabase

Are there any references that need to be added to have this code run and not error when debugging?

[TheShabz]

Outside of the DAO object library, I'm not sure.

[Juan4412]

More than likely not, I Just wanted to throw it out there to double check. Thanks for your help!

[Juan4412]

Would it actually be a viable option to have it pop up and ask what to update the value to? IDK if this would work, but in my update query, can I somehow have it ask, when the button is clicked what to update the value to?