Creating an access table from an ODBC connection

[rjgst3]

VB scripting.

Currently, I save a passthrough query that utilzes ODBC and use it to
run a make table table query.

select sql_odbc.* into access_table from sql_odbc;

I want to avoid maintaining any objects, i.e. the passthrough query,
in access and strickly use VB to create a table specific to a report.
To make a long story short, I want to create a temp_table from an ODBC
database, export the results to access, transfer the table via
'transferspreadsheet' and save it to my ftp location then delete the
temp_table. Now, I know how to export the report and save it to an ftp
location, I just need to learn how to initially create the table from
the results of an ODBC query via VB. Hope that makes sence.

Rick

 

[John Vinson]

VB scripting.

Currently, I save a passthrough query that utilzes ODBC and use it to
run a make table table query.

select sql_odbc.* into access_table from sql_odbc;

I want to avoid maintaining any objects, i.e. the passthrough query,
in access and strickly use VB to create a table specific to a report.
To make a long story short, I want to create a temp_table from an ODBC
database, export the results to access, transfer the table via
'transferspreadsheet' and save it to my ftp location then delete the
temp_table. Now, I know how to export the report and save it to an ftp
location, I just need to learn how to initially create the table from
the results of an ODBC query via VB. Hope that makes sence.

Rick

Why the extra step, given that you can TransferSpreadsheet based on a
select query of the ODBC table?

I'm not objecting - just asking. It just seems that the intermediate
table serves no purpose!

John W. Vinson[MVP]

 

[rjgst3]

Making the temp_table serves another purpose, I have template reports
where I copy and paste the data to the template then 'Save As' to the
FTP location via VB. I use the Transferspreadsheet method is other
cases though....

 

[John Vinson]

VB scripting.

Currently, I save a passthrough query that utilzes ODBC and use it to
run a make table table query.

select sql_odbc.* into access_table from sql_odbc;

I want to avoid maintaining any objects, i.e. the passthrough query,
in access and strickly use VB to create a table specific to a report.
To make a long story short, I want to create a temp_table from an ODBC
database, export the results to access, transfer the table via
'transferspreadsheet' and save it to my ftp location then delete the
temp_table. Now, I know how to export the report and save it to an ftp
location, I just need to learn how to initially create the table from
the results of an ODBC query via VB. Hope that makes sence.

Rick

Well, how about:

Dim strSQL As String
strSQL = "SELECT " & strSource & " INTO " & strTarget & " FROM " _
& strSource
DoCmd.RunSQL strSQL
<do all the other stuff>

John W. Vinson[MVP]

 

[rjgst3]

I'm confused. When do I use the connection string to connect to my
external datasource via ODBC to gather my information? EX:
conn.ConnectionString = "dsn=" & DBase & "; uid=" & User & "; pwd=" &
PWD

 

[Ron Hinds]

John is assuming that the ODBC table is linked in the Access db. This must
be the case becasue that's the only way a Query object would be able to
access the table, too. The bottom line is you need to link the ODBC table in
Access. Access then stores the ODBC connection string and uses it
automatically - no need to code it.

 

[rjgst3]

That's the goal, permanant access object, I want to accomplish in VB
what you would with either an ODBC linked table or an ODBC passthrough
query.

 

[rjgst3]

I want to perform this process in VB where I don't need to link any
tables to my access mdb. I objective is to have no access objects,
just modules.