Execute a query on table in different db that will make a table in currentdb

45 pts.
Tags:
Access 2007
Database
Visual Basic
Visual Basic .NET
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
ASKED: June 22, 2009  9:55 PM
UPDATED: June 23, 2009  2:46 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following