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
Software/Hardware used:
Software/Hardware used:
ASKED:
June 22, 2009 9:55 PM
UPDATED:
June 23, 2009 2:46 PM
RELATED QUESTIONS
- Pros & cons to have a typical star schema but with only one fact value field and a thousand attribute names in one of the dimension tables?
- AS/400 and Oracle Access Manager
- SQL Server 2008 SSIS Update Command Performance Issue
- database querying question SQLite
- Working with SQL: Extracting Data or Text within a Query, adding new column for extracted data
Answer Wiki:
You could create a link to the other database dynamically using the createtabledef method too.
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
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
