September 11, 2008 at 11:58 am
We are now receiving (via ftp) an MS Access database (an mdb file) from a client, and we are expected to query it for data to be inserted into our SQL Server db. Since both will reside on the same server (or so the current thinking goes), is this going to be difficult? Is there anything that needs consideration for this to work? Does the fact that the data is coming from an Access database complicate things? Can you give me an example of the types of queries that I would need?
Thanks for the help.
September 16, 2008 at 6:35 am
You need to look at either of "DTS Designer, Transform Data Task Properties" or OPENROWSET in BOL.
The OPENROWSET pages show an example (example C) of how to select data from an access database.
Hth,
September 17, 2008 at 4:39 am
Any reason why you would not be able to simply define the .mdb database as a linked server, and query it directly? So long as the file location, name, and format are preserved you will likely be able to continue referencing the latest version with the same link.
September 17, 2008 at 5:15 am
I wish that was possible, but the mdb is coming from an outside company that doesn't want to give us access to their servers (except for the small nightly window for ftp).
September 17, 2008 at 5:53 am
I don't understand -- if you wind up with a copy of their .mdb file where your SQL Server can reach it, does it matter how it got there (FTP, diskette, US Mail)?
September 17, 2008 at 6:11 am
We do simlar processes - download an access db to our server using ftp and import the access db into SQL - without any problems. This could work for you, as well setting up a linked server to the access db that you had downloaded with ftp. If necessary, the ftp process can rename (or copy) the database to a stable name.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply