January 23, 2002 at 10:26 pm
Hi,
i have a situation where i need to connect to a access db(Access 2000) in a different location and get data and update it to a SQL server 7.0 db. this is a daily routine.
i have implemented using a vb exe application and scheduled it to run daily.
can i write a SQL stored procedure to do the same task ...without using vb ? is it possible ?
can anybody help me on this ?
thanks in advance
Ravi.
Edited by - sayravi on 01/31/2002 02:03:48 AM
January 23, 2002 at 11:14 pm
Most likely you can. But without knowing exactly what operations you are performing it is hard to say. If you're doing typical SQL queries, then you can set up the Access .mdb as a linked server from SQL Server, write your stored procedure, and execute it as a job automatically.
I have to do this with our Citrix MetaFrame RM databases. For whatever reason, the counters and application sessions are stored in .MDB files on the local Citrix servers. So I've set up a linked server from SQL Server and copy the Access .MDBs over one at a time and import the relevant information using the linked server.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 24, 2002 at 5:03 am
You could also link the relevant SQL tables into Access and do the work from there. Access supports VBA, so most of what you can do in a VB app you can do there. If you're feeling adventurous you could even replicate the Access db to SQL!
Andy
January 31, 2002 at 2:25 am
quote:
Most likely you can. But without knowing exactly what operations you are performing it is hard to say. If you're doing typical SQL queries, then you can set up the Access .mdb as a linked server from SQL Server, write your stored procedure, and execute it as a job automatically.I have to do this with our Citrix MetaFrame RM databases. For whatever reason, the counters and application sessions are stored in .MDB files on the local Citrix servers. So I've set up a linked server from SQL Server and copy the Access .MDBs over one at a time and import the relevant information using the linked server.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Hi
thanks for ur reply. i tried adding access db as linked server. but i got an error message saying "error 7303:could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'"
i've tried with different options. but no use
any idea about this ?
thanks
Ravi
January 31, 2002 at 7:21 pm
There is a Microsoft Knowledge Base article that seems applicable to the error you are getting. Take a look at this and see if it matches your situation:
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q285833
Let us know.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply