Use ADO to join a free dbf table with a table in dbc database.

How can I use ADO to join a free table dbf with a table in the dbc database. ADO can only establish one connection, however the database and the free table are in different location. What can i do ??
twong84Asked:
Who is Participating?
 
BlackSharkCommented:
ADD TABLE Command
Adds a free table to the current database.
ADD TABLE TableName | ?
  [NAME LongTableName]
i'm not sure odbc driver supports this, but you can try:
YourConnection.Execute("ADD TABLE YourTable")
0
 
DRRYAN3Commented:
Create another ADO connection for your free table and open a recordset for the free table referencing your new ADO connection.
0
 
twong84Author Commented:
Do you mean open the recordset for the free table referencing the old ADO connection for the database ???.
How can i reference to another ado connection ???
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DRRYAN3Commented:
Are you trying to join the two tables into a single recordset with an SQL query?  If not, declare two connection objects:

dim strConnect as string
dim strSQL as string
dim cn1 as new adodb.connection
dim cn2 as new adodb.connection
dim rs1 as new adodb.recordset
dim rs2 as new adodb.recordset

strConnect = "Connection String for First Connection"
cn1.open strConnect
strConnect = "Connection String for Second Connection"
cn2.open strConnect

strSQL = "SELECT * FROM Table1"
rs1.open strSQL, cn1, adOpenForwardOnly, adLockReadOnly
strSQL = "SELECT * FROM Table2"
rs2.open strSQL, cn2, adOpenForwardOnly, adLockReadOnly

0
 
twong84Author Commented:
Yes, I am trying to join two tables into a single recordset with an SQL query.
However, one table is a free table(dbf). Another table is in the dbc database.

Any suggestion ???
0
 
twong84Author Commented:
Yes, I am trying to join two tables into a single recordset with an SQL query.
However, one table is a free table(dbf). Another table is in the dbc database.

Any suggestion ???
0
 
DRRYAN3Commented:
Still looking, but one suggestion is to use an Access MDB and link to the various DBF files.  Set your connection to the .MDB file and query away.  If you want to create this type of setup in code, see http://support.microsoft.com/support/kb/articles/Q230/5/88.ASP?LN=EN-US&SD=gn&FR=0&qry=tables&rnk=15&src=DHCS_MSPSS_gn_SRCH&SPR=ADO
0
 
twong84Author Commented:
Thanks DRRYAN3, that's what in my mind too.
I appreciate alot for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.