Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
anderskd
Starting Member
25 Posts |
 Posted - 2009-09-03 : 19:01:44
|
| Hello All,I have a project where I will have to go to a client site and pull data from an AS400. I have done this in the past internally with SQL by setting up the ODBC drivers and running openrowset queries to drop the data into an empty SQL database. From there I can take it and use the data within SQL. I would like to essentially the same with except, drop the data into an Access database. I don't believe I will be able to get use of a SQL database.I know I can get external data from Access using the ODBC DSN I have set up. However, I can't really do this each time I need to pull data (I need about 150 tables out of 800 each time - and filter the data for size issues). I would like to be able to generate scripts to pull the data for only the tables I need and have the ability to filter the data as I bring it in.Is there anyway from an Access query I can pull data from one of my ODBC sources? I would assume I would have to qualify the AS400 library and tables and probably even username and pw. The ability to automate this and filter this would be really helpful.Here is what I have run in the past on the SQL box.SELECT A.* INTO ABC FROM OPENROWSET('MSDASQL','DSN=EXAMPLEDSN;UID={EXAMPLEUSERACCT};PWD={EXAMPLEPW}', 'SELECT * FROM AS400MACHINE.LIBRARY1.ABC WHERE APJ IN (''P1'',''P2'')') AS AThanks much to anyone that might help!Kelly |
|
|
|
|
|
Pulling Data into Access