Client Access ODBC & SQL

[nitemarepc]

I want to use the Client Access ODBC to connect to the AS400 and run an sql to create a work table on the local c:. I can connect and create a query with a sql but cannot create a local table with the sql. HELP!!

 

[fredericofonseca]

Client Access Will allow you to create/access tables on the 400.

If your intention is to download a table from the AS400 into your local drive then you can either use the existing CA transfer program, or create the local "table" on your choice of DB's available for Windows.

Note that you were not very clear on how your local table should be, so please explain what you're trying to do and we may be able to give you a solution.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[nitemarepc]

I need the table on the local drive to be in .mdb (Access) format. I will be using the tables to generate a report in VB6 Crystal Light.

 

[fredericofonseca]

Then you create two connections.

one for CA-400 and the other for MS-Access.
You then loop through the CA-400 recordset and for each record you insert into the Access one.

Not sure if you can replace the loop with a insert from recordset.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[bjd4jc]

I am not familiar with CA but if it is ODBC then couldn't nitemarepc create a DSN and then link to the tables on the 400 and then do a SELECT * INTO [NewTable] FROM [400 Table] and accomplish the same thing without the need for the loop?

 

[nitemarepc]

I tried the SELECT * INTO [New Table] FROM [400 Table] and I get the error of ODBC connection failed. I could loop through the recordset for the query of the 400 table but we are talking about a huge table. I thought there should be a way to bridge the 400 table into a new Access type table on the local c-drive. I have multiple files with multiple operations to perform to get the data needed for the reports and it would be more efficient and timely to do this by filtering the 400 tables with the sql to local temp tables and finish processing on the temp tables for the reports.

 

[fredericofonseca]

nitemarepc,

are you saying that you created a ODBC source to the 400, and that you then created a linked table on access?
And that this then failed when you've done the "select"?

If so can you please post the error message, CA driver version and OS400 version used.
After I have this I can look into it. I have done this before and it does work.

If you are doing this from VB then you need to do as I stated.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[nitemarepc]

I am using VB6. I have found someone who says this can be done with ADO with an ODBC connection to both the 400 tables and Access.