mcs1169
asked on
How do I import tables from an ODBC database
Hi all. On a daily basis, I need to copy 7 tables from a SQL database to an Access database. The process that I go through is such:
Open the access db --> File --> Get External Data --> Import --> Select my ODBC database and log in --> Select my 7 Import Objects (tables) and OK to start.
I would love to create a macro or a DTS package to do this but am very short on knowledge in that area.
Any help is appreciated.
Mitch
Open the access db --> File --> Get External Data --> Import --> Select my ODBC database and log in --> Select my 7 Import Objects (tables) and OK to start.
I would love to create a macro or a DTS package to do this but am very short on knowledge in that area.
Any help is appreciated.
Mitch
ASKER
The access db is being used by a person who has no access (nor do we want him to have access) to the SQL db (on a production server) so I do not think that links will help.
In regards to the VBA script, no clue. Along with being short on the finer details of Access management, I haven't a clue when it come to VBA. Sorry (sigh).
In regards to the VBA script, no clue. Along with being short on the finer details of Access management, I haven't a clue when it come to VBA. Sorry (sigh).
OK, so you do it for them??
Do you log onto SQL using a trued connection, or with username/password (SQL authentication)?
Will have top give you some VBA to put behind a command button. Do you want it to prompt for your login or cann it be stored (in which case anyone can run it)?
Kelvin
Do you log onto SQL using a trued connection, or with username/password (SQL authentication)?
Will have top give you some VBA to put behind a command button. Do you want it to prompt for your login or cann it be stored (in which case anyone can run it)?
Kelvin
ASKER
Yep. We just give him what he needs. He did have access until he brought the server to it's knees with an endless loop in some code that he threw together. He has upper management OK to do the hacking...er....I mean.... programming.
We use SQL Authentication. I would prefer to be prompted since he has full access to the MSA database. However, if you feel ambitious, please provide both solutions and I will confer with my partner to see what she would prefer.
We use SQL Authentication. I would prefer to be prompted since he has full access to the MSA database. However, if you feel ambitious, please provide both solutions and I will confer with my partner to see what she would prefer.
ASKER
Can anyone help?
Sorry, missed your comment of about 8 days ago. Will review and come back with some code shortly
Kelvin
Kelvin
ASKER
Thank you, Kelvin.
What version of SQL Server are you connecting to, 2000,2005, 2008??
K
K
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh, if your not using SQL Server 2005 replace {SQL Native Client} with {SQL Server} for SQL 2000 (will also work for 2005) or {SQL Native Client 10.0} if using SQL Server 2008.
If usure, goto Control Panel> Administrative Tools, Data Sources(ODBC) and then select the File DSN, Click Add and scroll to end then of the drivers list and see what SQL ones are listes, use which ever one is the latest in the code. Cancel out of there to avoid creating a new DSN.
Kelvin
Kelvin
If usure, goto Control Panel> Administrative Tools, Data Sources(ODBC) and then select the File DSN, Click Add and scroll to end then of the drivers list and see what SQL ones are listes, use which ever one is the latest in the code. Cancel out of there to avoid creating a new DSN.
Kelvin
Kelvin
ASKER
Thank you for the time and effort on this. I have not had the chance to try it out but hope to soon.
A simpler method would be to create a permanent link to these, and if you cannot work on those links then create 7 "Make table queries" based on these 7 linked tables that make your local copies.
Run the 7 from VBA with setWarnings set to False while running so that they do not prompt you that they are deleting the former copy each time. (remember to setwarnings back to True at the end.
Kelvin