Click here to Skip to main content
15,867,762 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I need to connect to an Access table on SQL Server so that I can schedule an update to the Access table on a daily basis. How would I connect the Access table? I've seen stuff on how to connect an Access table to a SQL table, but nothing that just updates a field on an Access table from SQL Server.

This is what I am thinking is the correct syntax for the call;
C#
UPDATE OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Location of .mdb';'admin';'password', NameOfTable)
 SET STATUS='REVOKED', APPR_DATE=REVO_DT
 WHERE REVOKE_DT <= @DT AND MEDIA_STATUS='PENDREVO'

But then is gives me; The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
I'm not sure I'm doing this right. Any help?
Posted
Updated 13-May-14 3:36am
v2
Comments
Maciej Los 13-May-14 14:45pm    
Does MS SQL Server is 32 or 64 bit?
Liefie2000 13-May-14 15:06pm    
It's a 32bit machine.
Maciej Los 13-May-14 15:19pm    
OK.
Are you trying yo update MS Access database using values from SQL database?
Liefie2000 13-May-14 15:23pm    
No. All I'm doing is creating a stored proceedure in SQL Server that will run on a daily basis. This stored proceedure will connect to an Access database and table and update the status of a device from PendRevo to Revo once the date RevoDate on the Access table has been met.
Maciej Los 13-May-14 15:23pm    
Have you tried to fetch data from MS database using simple SELECT statement? Does it working?

1 solution

Yeah, you're doing it in proper way, but...
If SQL Server is installed on Windows 64 bit, you need to install and use ACE.OLEDB.xxx database engine redistributable[^]. Please, read this: Accessing Excel files on a x64 machine[^].

In reply to OP's comments...
The best way to update MS Access database via MS SQL server is to connect it using sp_addlinkedserver command: OLE DB Provider for Jet[^]
 
Share this answer
 
v2
Comments
Liefie2000 15-May-14 14:29pm    
It says I don't have rights to addlinkedserver.
Maciej Los 15-May-14 14:32pm    
Yeash, it needs admin rights to add linked server. Ask your SQL server guy.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900