access to SQL copy

Question

Hi Guys

We have an Access DB which has a set of local tables and input forms etc. in which a user maintains their data.

We also have a SQL DB with the same tables which is used to displays the data in a web search form.

What is the best way to allow the user to udate his changes to the SQL db while keeping the working copy local so he can work offline and then push the files when he is happy with new version of the data?

My first thought was add the SQL tables as linked tables I could then truncate (access does like that) or delete the content in each table and then do an insert for each table.

Can I call a SP from access on the SQL to truncate the tables as I am have problem running deletes

I really do want to get it down to the user running a macro/sql call that is repeatable etc.

Thanks for your help

Answer 1

You should be able to use the ADODB.Command object to execute stored procedures.

EDIT:

This example is copied from Using ADO in VB and Access

Sub ADO_COMMAND_CONNECTION_TEST()
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String

    cmd.ActiveConnection = " DRIVER={SQL Server};" & _
      "Server=UKDUDE;DATABASE=pubs;UID=sa;PWD=;"
    cmd.CommandText = "byroyalty"
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Refresh
    cmd.Parameters(1).Value = 25

    Set rs = cmd.Execute

' Recordset now has authors with 25% royalty.....

End Sub

Answer 2

Don't ever use MS Access linked tables with MS SQL.

Not only are they slow, but Access can leave open client-side write cursors on the tables referenced. That's a really dumb way to create lots of deadlocks, but Access does it anyway.

Microsoft significantly improved this when they added Access Data Projects - in these the entire back end is replaced with SQL and Access just supplies the forms.

If you want user actions to write directly back then ADPs are by far the best method.

If you want to cache changes locally in your Access DB and then send them up to SQL you have a far more complex problem. You will need to be far more specific on exactly how you want synchronisation to happen - for instance if two users make offline changes who wins when they connect?

Answer 3

I don't understand why you just don't link directly to the SQL Server data and use it directly, rather than going to the trouble of maintaining a second copy of it. This is the standard Access way to do things -- why are you resisting the natural capabilities of the tool you're using?

--
David W. Fenton
David Fenton Associates