Help with MS Access and SQL Server 2008

Question

Hi, I need somebody to point me to the right direction, I have a MS Access DB that is updated by HP devices, and I have to sync it with the SQL Server 2008.

I have a few Ideas, and I would like to know what do you think about this:

1. Is there anything like triggers on access? if so can I comunicate with a SQL Server?
2. Is there any way to use VBA so access tell my VBA macro or whatever to make an update on SQL Server?
3. Is there a simple way to connect from VB 6 to SQL Server 2008?
4. Using a script that run at background and check DB at X minutes or seconds.

Any other ideas or suggestions are very welcome.

Thanks and like always sorry for the english.

Answer 1

You can try to link MS Access database to SQL server,
Now you can querying data from SQL server which is in MS Access.
I do not know about trigers on MS ACCESS but you can implement some loops in MS SQL to periodicity count or select data for cheking new one.
To make linked server in SQL MGM Studio on Object Explorer -> Server Object -> Linked server -> right click -> New linked server After then in new query simple call any table like

Select * from [linked server].dbo.mytable

In MS SQL there is WAITFOR command which You can implement

Answer 2

Just to add a few points to adopilot’s answer

1) Access 2010 does have triggers and stored procedures but they are more about native access/jet tables as opposed to linked SQL tables I believe.

2 & 3) If you want to connect VB6 or VBA to an SQL server then the technology to do that is called ADO for example here is some code to open a connection and run a SQL statement

Dim dbCon as NEW ADODB.Connection
dbCon.ConnectionString = strSQL_con_string
dbCon.Provider = "sqloledb"
dbCon.Open

dbCon.Execute “UPDATE tblFoo SET bar=5 WHERE Foo=1”

dbCon.Close

4) You can either do this client side with a timer/wait event in VB6/Access or do it server side with a SQL job, not sure which is best for your situation given the limited information provided

Answer 3

You can refer to either the SQL Server database or the MS Access database inline in your SQL:

UPDATE SQLTable (ID, Stuff) 
SELECT ID, Stuff
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'c:\External\MyAccess.mdb';'admin';'', Table1) 

-- From databasejournal

You can execute this query using ADO with a connection to SQL Server

-- Connection strings

You can also do the same from the Access end with ODBC

Dim cn As New ADODB.Connection

scn = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" _
    & DBFullName
cn.Open scn

s = "INSERT INTO [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server\Instance;Trusted_Connection=Yes;" _
& "DATABASE=test].Table2 (ID, Stuff) SELECT ID, Stuff FROM Table1"
cn.Execute s

You can run ADO with VBScript, or other suitable script and use Windows Task Scheduler to kick the script off at suitable intervals. This is not without pain.