Remote Update from Local Table - VBA Access

[StephNicolaou]

Hey all,

I have split an Access database not on the server. I have decided to VBA code the links, however I have reached a stall when attempted to VBA code the update query; updating the remote table with data from the local temporary table.

I have tried multiple things and the final state it is in is shown below:

DoCmd.RunSQL ("UPDATE TBLRemote IN '" & path & "', TBLLocal  SET  " & _
              "(TBLRemote.[UpdateField] = [TBLLocal]![UpdateField]+1 WHERE  . . .) ;")

path is the connection to the remote database, however this is not correct syntax. I am looking for advise on the correct syntax.

I would like to figure out how this fairly old method was carried out... Tips would be very much appreciated.

[BitBlt]

The syntax you're trying to use is only valid when setting the Source property on a form. You can't use it in code (see the MSAccess documentation on "Source Property").

However, you can use ADO and OLEDB to do this. It's really simple, just use this code snippet to pattern your connection and execution of your SQL statement:

Dim myConn As New ADODB.Connection

myConn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=u:\db5.mdb")

myConn.Execute ("update table1 set myData = 'Sam' where myData = 'Fred'")

myConn.Close

Set myConn = Nothing

No ODBC data source to worry about. Hope this helps! Good luck!

[StephNicolaou]

Thanks for your help.

Access does not let me use ADO/OLEDB/ADODB .Connection due to the 'user-define type not defined'.

Your syntax does look correct although it's taken out the complexity of the remote and temp local table being in separate locations.

It seems as though manullay coding the backend links is too much trouble for what it's worth. Although it might be easier to run the queries straight from Access...

[BitBlt]

That's odd that it won't allow you to use that connection object. Although, I am stuck using Access 2003 rather than the latest version, so it might be different, or you might have to go to the references/components options in the VBA IDE.

You might, however, consider pre-linking the remote table, meaning manually "get external data->link tables" from the menu. Then you can create an update querydef on the fly, if you have to do bulk updates. Have you worked with dynamically building querydefs? Not the prettiest solution, but it should work.

[StephNicolaou]

Thanks a lot BitBlt! I got it to work using QueryDefs :)

All I had to do is create the temporary table in the backend table, which made updating much easier, rather than updating a remote table with a local table. Then the update worked fine using the QueryDefs method.

Now I just have to do this throughout my whole database, all in the name of security.

There isn't a lot of information on how to do this on the web so I've posted my code syntax for anyone else looking to do this.

Set up the path to the back-end database:

Dim path As String
path = "C:\BackEndDatabase.mdb"

Set the database to the back-end database:

Set db = OpenDatabase(path)
Dim qDef As QueryDef

Create the update query in the back-end database using QueryDefs:

Set qDef = db.QueryDefs("UpdateQueryInBackEnd")

Within my queries that created the temp table, I added IN '"& path &"' to create the temporary table in the back-end database:

DoCmd.RunSQL ("SELECT DISTINCT [TBLs] INTO TEMP IN '" & path & "' FROM [TBLs] IN '" & path & "' WHERE  [. . .] ;")

Now when updating the tables using QueryDefs, it worked fine:

qDef.sql = "UPDATE TBL1, TEMP SET TBL1.Score = [TEMP]![NewScore] WHERE  [. . .]"
qDef.Execute
qDef.Close

Lesson learned: Insert and Update queries do not work with DoCmd.RunSql when inserting into or updating a remote database. These only work when QueryDefs are used.

Thanks,
Cleo123

PS. I also found this website very useful: http://www.upsizing.co.uk/Art26_RecordSets.aspx#top