June 28, 2012 at 8:54 am
We have a vendor system here whose back-end is Cache (Mumps). They developed an Access front end that exposes the mumps globals as linked tables in Access. I was hoping to import table(s) from the Access front end to SQL Server. I opened the Import/Export wizard and all that was exposed to me were the Access local tables. Aren't Access linked tables available for export?
Thanks all.
June 28, 2012 at 9:07 am
how about in access running a maketable query based on the linked table and then pushing that to your sql server ?
MVDBA
June 28, 2012 at 9:33 am
This is really an odd one. The vendor's client software has to be installed on the PC that opens Access. The Access autoexec macro runs their software that links the Cache globals as tables. So one has to open the Access database and log in in order to get the globals linked as tables. SSIS would allow me to import an Access table (if I could see it). An Openrowset query tells me the .mdb is already opened by another user. Another wrench is that we want to get some data out of Access and into a 64-bit SQL Server. The folks here that support the app can dump out data to a flat file for import. But I was going to try and do it all in an SSIS package. I was just really suprised that only local tables were exposed and hoping that somebody more knowledgable in Access knows if that is a table property or something along those lines that can be changed.
June 28, 2012 at 12:28 pm
There's a lot of data here. I think a make-table query would push the Access limits. And it seems like it would be an additional step. I can't think of anything else but to have an Access query dump to flat file(s) and import them.
July 3, 2012 at 10:35 am
If you have linked tables then we are almost certainly talking about ODBC drivers, look at the definition in the access database and the definition in the ODBC setup, this would likely give you enough detail to link SSIS directly to the data WITHOUT going through access.
CEWII
May 21, 2014 at 6:50 pm
Sorry to resurrect this one, but I am looking to get an answer on this too. My issue with going directly to the ODBC is that some of my data has dates that are pre 1573 (due to data entry errors, but they are still there and unavoidable). So my question would then become can this data be imported into SSIS and changed before importing to a SQL Table?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply