By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

need help with importing a Table from another access database in code

P: n/a
I have code to allow the user to select any .mdb of his choice on the HD,
but how do I then let him select a table from within this .mdb and then
create a destination table of a name that I assign in code.

DoCmd.TransferDatabase acImport, "Microsoft Access", anymdb, acTable,
anychoicetable, "fixedtablename"

So I would like this command to prompt the user so the user can select the
table
Thanks in advance
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Danny,

Look at TableDef in the help file. You will need to use the TableDef collection
to get a list of the tables in the .mdb. You will then have to write some code
where the user can select the table name from the collection and assign it to a
string variable such as SelectedTbl. Then in your TransferDatabase expression,
you will put SelectedTbl where you have anychoicetable.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Danny" <da********@hotmail.com> wrote in message
news:D2**********************@news4.srv.hcvlny.cv. net...
I have code to allow the user to select any .mdb of his choice on the HD,
but how do I then let him select a table from within this .mdb and then
create a destination table of a name that I assign in code.

DoCmd.TransferDatabase acImport, "Microsoft Access", anymdb, acTable,
anychoicetable, "fixedtablename"

So I would like this command to prompt the user so the user can select the
table
Thanks in advance

Nov 12 '05 #2

P: n/a
Danny,

Here's some code by Joe Black I just found in another newsgroup. It's almost
exactly what you need!

Steve
PC Datasheet

I use the following code to populate a combo box with the table names in the
current database (excluding system tables):

'*** code start ***
Private Sub Form_Open(Cancel As Integer)
Dim strRowSource As String
Dim TblDef As TableDef

Set db = CurrentDb
For Each TblDef In db.TableDefs
If InStr(TblDef.name, "Sys") = 0 Then
strRowSource = strRowSource & "'" & TblDef.name & "';"
End If
Next
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
cboTableDefs.RowSource = strRowSource

Set db = Nothing
End Sub
'*** code end ***

and when I select a table name from the combo box, the following code puts
all the field names from the selected table into a listbox:

'*** code start ***
Private Sub cboTableDefs_AfterUpdate()
Dim Feeld As Field
Dim strRowSource As String

If IsNull(cboTableDefs) Then Exit Sub

Set TblDef = db.TableDefs(cboTableDefs)
For Each Feeld In TblDef.Fields
strRowSource = strRowSource & "'" & Feeld.name & "';"
Next
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
lstFields.RowSource = strRowSource
lstFields.Requery
End Sub
'*** code end ***

"Danny" <da********@hotmail.com> wrote in message
news:D2**********************@news4.srv.hcvlny.cv. net...
I have code to allow the user to select any .mdb of his choice on the HD,
but how do I then let him select a table from within this .mdb and then
create a destination table of a name that I assign in code.

DoCmd.TransferDatabase acImport, "Microsoft Access", anymdb, acTable,
anychoicetable, "fixedtablename"

So I would like this command to prompt the user so the user can select the
table
Thanks in advance

Nov 12 '05 #3

P: n/a

"PC Datasheet" <sp**@nospam.spam> wrote in message
news:wz**************@newsread2.news.atl.earthlink .net...
Danny,

Here's some code by Joe Black I just found in another newsgroup. It's almost exactly what you need!

Steve
PC Datasheet

I use the following code to populate a combo box with the table names in the current database (excluding system tables):

'*** code start ***
Private Sub Form_Open(Cancel As Integer)
Dim strRowSource As String
Dim TblDef As TableDef

Set db = CurrentDb
For Each TblDef In db.TableDefs
If InStr(TblDef.name, "Sys") = 0 Then
strRowSource = strRowSource & "'" & TblDef.name & "';"
End If
Next
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
cboTableDefs.RowSource = strRowSource

Set db = Nothing
End Sub
'*** code end ***

and when I select a table name from the combo box, the following code puts
all the field names from the selected table into a listbox:

'*** code start ***
Private Sub cboTableDefs_AfterUpdate()
Dim Feeld As Field
Dim strRowSource As String

If IsNull(cboTableDefs) Then Exit Sub

Set TblDef = db.TableDefs(cboTableDefs)
For Each Feeld In TblDef.Fields
strRowSource = strRowSource & "'" & Feeld.name & "';"
Next
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
lstFields.RowSource = strRowSource
lstFields.Requery
End Sub
'*** code end ***

"Danny" <da********@hotmail.com> wrote in message
news:D2**********************@news4.srv.hcvlny.cv. net...
I have code to allow the user to select any .mdb of his choice on the HD, but how do I then let him select a table from within this .mdb and then
create a destination table of a name that I assign in code.

DoCmd.TransferDatabase acImport, "Microsoft Access", anymdb, acTable,
anychoicetable, "fixedtablename"

So I would like this command to prompt the user so the user can select the table
Thanks in advance



Thakns, this is great.

I will apply this my app.

Danny

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.