Running ODBC query through Access on multiple databases

[Guest]

I would like to know if there is a way of running a query on databases
located on physically different computers (AS/400) without having to first
re-link the databases everytime you want to run the query on a different
machine. My Access query queries the exact same table names on each machine
but the databases are physically located on different machines.
Currently, the only way I know to do it is to FIRST go into the OBDC driver,
define the new machine, then go into the Access Link Manager and re-link the
tables to the new machine THEN run the query.
It would be nice if I can just run the query and the query asks you what
machine you want to connect to (you can have a drop down menu of the
different machines) then the query does all the "legwork" connecting the
machine through OBDC, re-assigning the links to the tables, then running the
query.

Thanks

 

[MGFoster]

I would like to know if there is a way of running a query on databases
located on physically different computers (AS/400) without having to first
re-link the databases everytime you want to run the query on a different
machine. My Access query queries the exact same table names on each machine
but the databases are physically located on different machines.
Currently, the only way I know to do it is to FIRST go into the OBDC driver,
define the new machine, then go into the Access Link Manager and re-link the
tables to the new machine THEN run the query.
It would be nice if I can just run the query and the query asks you what
machine you want to connect to (you can have a drop down menu of the
different machines) then the query does all the "legwork" connecting the
machine through OBDC, re-assigning the links to the tables, then running the
query.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use DSN-less ODBC connect strings. See these sites for the strings:

http://www.connectionstrings.com/
http://students.ukdw.ac.id/~sonny/odbc_dsnless.html
http://aspwebsolution.com/articles/esqel/odbc_dsnless.htm

You can change the query's ODBC connect string like this (the query has
to already be a SQL pass-thru query):

< using DAO >
CurrentDB.QueryDefs("your query's name").Connect = strNewConnect

I've never had good luck w/ AS400 systems. They seem to want the DSN.
Perhaps your situation is different.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBREWViIechKqOuFEgEQIeMwCfSVMohcZyttiWOZ2sBSh+DC10brIAoIrC
akJgbjWmvtG4KvVuXvL46eut
=FH2t
-----END PGP SIGNATURE-----

 

[Guest]

Thank You. Would your connection string allow me to run the Pass-through
query and have it ask me "What system to connect to?" and I can use a drop
down menu or type in the system I want to connect to? We have about 50
different systems (one for each location we query). BTW: Your response was
VERY helpful. I appreciate the weblinks on connection strings.

 

[MGFoster]

You could create a form that would have the systems in a Combo Box list.
When the user selects a system, there would be VBA code that creates
the Connect string & places it in the QueryDef. Then run the query.

 

[Guest]

Thanks. I will have to experiment with that to see how to do that. ( I am
not very good with VBA). However, after receiving your first response, I
started experimenting with pass-through queries and when I click on the query
to run it, it brings up the OBDC window to choose a DSN. I guess I can
create a DSN definition for each system (50 of them) and pick the right DSN
from the list when the query runs. This might be easier (for me) than using
VBA.
There is another problem, however. After choosing the DSN definition for
the system I wanted to connect to and running the query, I get an error that
the query cannot find a table on the system. I think the error is because I
have not created a link to the tables on the AS/400 system. DO I have to
create a link (using "Link Manager") to each table in the query for EACH
system I want to connect to? If a query uses 3 tables, that would be 50x3 or
150 tables I would have to link to and that is just for ONE query. I have
several queries. The tables have the format <libname>.tablename.fieldname.
The <libname> is slightly different for each system based on a numerical
numbering of the systems <libname[sys#]>.
Is there a way to dynamically (at runtime) link to the correct tables on the
correct system? Is there an SQL statement to link to a table? I know there
is a "CREATE TABLE" SQL statement but I don't know about a "link table" SQL
statement.
I apologize for having to "pick your brain" like this but I have thought of
everything I know to no avail.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You do not have to link to tables you are using in a Pass-Thru query.

If you're getting the error that there isn't any table name, either the
table name is misspelled in your query or you're not using the proper
syntax when identifying the table name. Standard SQL syntax is:

<server or machine name>.<database name>.<owner>.<table name>

E.g.:

SYS3344.Accounting.Anthony.AccountsPayable

It may be different for AS400. Remember, I said I had problems w/ AS400
systems and DSN-less connections. I usually ended up using the DSN.

AS400 is really IBM's DB2 database system. If you can find some
documentation about that system on AS400s it might prove useful.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBREaaNoechKqOuFEgEQJgCwCfZAnMSeUKjAk2eghsrf/N/cDXJgkAnjYC
qp/OJ2RQPCQZ6GrDHlaR7nMV
=z7wS
-----END PGP SIGNATURE-----

Thanks. I will have to experiment with that to see how to do that. ( I am
not very good with VBA). However, after receiving your first response, I
started experimenting with pass-through queries and when I click on the query
to run it, it brings up the OBDC window to choose a DSN. I guess I can
create a DSN definition for each system (50 of them) and pick the right DSN
from the list when the query runs. This might be easier (for me) than using
VBA.
There is another problem, however. After choosing the DSN definition for
the system I wanted to connect to and running the query, I get an error that
the query cannot find a table on the system. I think the error is because I
have not created a link to the tables on the AS/400 system. DO I have to
create a link (using "Link Manager") to each table in the query for EACH
system I want to connect to? If a query uses 3 tables, that would be 50x3 or
150 tables I would have to link to and that is just for ONE query. I have
several queries. The tables have the format <libname>.tablename.fieldname.
The <libname> is slightly different for each system based on a numerical
numbering of the systems <libname[sys#]>.
Is there a way to dynamically (at runtime) link to the correct tables on the
correct system? Is there an SQL statement to link to a table? I know there
is a "CREATE TABLE" SQL statement but I don't know about a "link table" SQL
statement.
I apologize for having to "pick your brain" like this but I have thought of
everything I know to no avail.

:

You could create a form that would have the systems in a Combo Box list.
When the user selects a system, there would be VBA code that creates
the Connect string & places it in the QueryDef. Then run the query.

 

[Guest]

Thanks a lot. I tried it and it WORKED. I really appreciate all your help.
I was defining the tables as <libname>.<tablename>.<fieldname>. All I had to
do was take out the <libname>, define the libname in the DSN definition and
VIOLA it worked. Now, I think I still need to define 50 DSN definitions, one
for each AS/400 I need to connect to, but that is mostly a cut and paste
job. Now if I can just figure out how to change pass-through query
parameters during runtime (like date ranges and account number ranges) I will
be SET. Again, thanks a lot.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You do not have to link to tables you are using in a Pass-Thru query.

If you're getting the error that there isn't any table name, either the
table name is misspelled in your query or you're not using the proper
syntax when identifying the table name. Standard SQL syntax is:

<server or machine name>.<database name>.<owner>.<table name>

E.g.:

SYS3344.Accounting.Anthony.AccountsPayable

It may be different for AS400. Remember, I said I had problems w/ AS400
systems and DSN-less connections. I usually ended up using the DSN.

AS400 is really IBM's DB2 database system. If you can find some
documentation about that system on AS400s it might prove useful.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBREaaNoechKqOuFEgEQJgCwCfZAnMSeUKjAk2eghsrf/N/cDXJgkAnjYC
qp/OJ2RQPCQZ6GrDHlaR7nMV
=z7wS
-----END PGP SIGNATURE-----

Thanks. I will have to experiment with that to see how to do that. ( I am
not very good with VBA). However, after receiving your first response, I
started experimenting with pass-through queries and when I click on the query
to run it, it brings up the OBDC window to choose a DSN. I guess I can
create a DSN definition for each system (50 of them) and pick the right DSN
from the list when the query runs. This might be easier (for me) than using
VBA.
There is another problem, however. After choosing the DSN definition for
the system I wanted to connect to and running the query, I get an error that
the query cannot find a table on the system. I think the error is because I
have not created a link to the tables on the AS/400 system. DO I have to
create a link (using "Link Manager") to each table in the query for EACH
system I want to connect to? If a query uses 3 tables, that would be 50x3 or
150 tables I would have to link to and that is just for ONE query. I have
several queries. The tables have the format <libname>.tablename.fieldname.
The <libname> is slightly different for each system based on a numerical
numbering of the systems <libname[sys#]>.
Is there a way to dynamically (at runtime) link to the correct tables on the
correct system? Is there an SQL statement to link to a table? I know there
is a "CREATE TABLE" SQL statement but I don't know about a "link table" SQL
statement.
I apologize for having to "pick your brain" like this but I have thought of
everything I know to no avail.

:

You could create a form that would have the systems in a Combo Box list.
When the user selects a system, there would be VBA code that creates
the Connect string & places it in the QueryDef. Then run the query.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Anthony wrote:

Thank You. Would your connection string allow me to run the Pass-through
query and have it ask me "What system to connect to?" and I can use a drop
down menu or type in the system I want to connect to? We have about 50
different systems (one for each location we query). BTW: Your response was
VERY helpful. I appreciate the weblinks on connection strings.

:



Anthony wrote:


I would like to know if there is a way of running a query on databases
located on physically different computers (AS/400) without having to first
re-link the databases everytime you want to run the query on a different
machine. My Access query queries the exact same table names on each machine
but the databases are physically located on different machines.
Currently, the only way I know to do it is to FIRST go into the OBDC driver,
define the new machine, then go into the Access Link Manager and re-link the
tables to the new machine THEN run the query.
It would be nice if I can just run the query and the query asks you what
machine you want to connect to (you can have a drop down menu of the
different machines) then the query does all the "legwork" connecting the
machine through OBDC, re-assigning the links to the tables, then running the
query.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use DSN-less ODBC connect strings. See these sites for the strings:

http://www.connectionstrings.com/
http://students.ukdw.ac.id/~sonny/odbc_dsnless.html
http://aspwebsolution.com/articles/esqel/odbc_dsnless.htm

You can change the query's ODBC connect string like this (the query has
to already be a SQL pass-thru query):

< using DAO >
CurrentDB.QueryDefs("your query's name").Connect = strNewConnect

I've never had good luck w/ AS400 systems. They seem to want the DSN.
Perhaps your situation is different.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBREWViIechKqOuFEgEQIeMwCfSVMohcZyttiWOZ2sBSh+DC10brIAoIrC
akJgbjWmvtG4KvVuXvL46eut
=FH2t
-----END PGP SIGNATURE-----