Q:
Execute a query on table in different db that will make a table in currentdb
Cheers,
I have an Access 2007 application that uses mostly vb.net code to create an excel spreadsheet with all the necessary data in the right places...
The program uses a specific set of data that resides in a different database. What I would like to do is connect to the foreign table (which I can do with .Connection) then run a something like .execute on a QueryDef with a 'select x into y' type statement, or some other copy statement. Where the table created would exist in currentdb.
It would be satisfactory to create a link to the foreign table via code but the link would have to be dynamic where I could select one from many tables (each table has one year of data). Then I could use a simple .execute on the QueryDef.
I am able to create a table via DAO.TableDef with .CreateTableDef and then run through each record of the ADODB.Recordset collection but that would take a while to complete and would be much longer than a simple select into statement or some other copy process.
Thank you in advance for any assistance,
Bob
I have an Access 2007 application that uses mostly vb.net code to create an excel spreadsheet with all the necessary data in the right places...
The program uses a specific set of data that resides in a different database. What I would like to do is connect to the foreign table (which I can do with .Connection) then run a something like .execute on a QueryDef with a 'select x into y' type statement, or some other copy statement. Where the table created would exist in currentdb.
It would be satisfactory to create a link to the foreign table via code but the link would have to be dynamic where I could select one from many tables (each table has one year of data). Then I could use a simple .execute on the QueryDef.
I am able to create a table via DAO.TableDef with .CreateTableDef and then run through each record of the ADODB.Recordset collection but that would take a while to complete and would be much longer than a simple select into statement or some other copy process.
Thank you in advance for any assistance,
Bob
ASKED:
Jun 22 2009 9:55 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
dim db as database, tdef as tabledef
set db = currentdb()
Set Tdef = db.CreateTableDef("Table2009")
Tdef.Connect = ";Database=C:\history.mdb"
Tdef.SourceTableName = "Table2009"
db.TableDefs.Append Tdef
Later you could simply delete the linked table object after using a query to append the records into your new table.
If you want to select a list of tables from the foreign table from a combo box, you could build the combo box's row source from the list of tables. It would be set up as a row source type of value list. Then you could put this in the combo box's got focus event.
Dim db As Database, x As Long, TableList As String
Set db = OpenDatabase("c:\history.mdb")
For x = 0 To db.TableDefs.Count - 1
If Left(db.TableDefs(x).Name, 4) <> "msys" Then
TableList = TableList & db.TableDefs(x).Name & ";"
End If
Next x
TableList = Left(TableList, Len(TableList) - 1) 'Remove the last ;
Me![Combo0].RowSource = TableList
On the combo box's after update event, you could use the same code as above; but use the combo box's value to supply the table to link
Dim db As Database, tdef As TableDef
Set db = CurrentDb()
Set tdef = db.CreateTableDef("Table2009")
tdef.Connect = ";Database=C:\history.mdb"
tdef.SourceTableName = Me![Combo0]
db.TableDefs.Append tdef
