TransferDatabase wont kick-start!

[Dave Gehrig]

Hi guys,

Using Access 2003 on XP.

First time caller, long time listener. I am not a programmer per se,
and all my training in coding in the past year or so has come from the
wealth of info in these newsgroups, but I've finally hit a brick wall
re importing tables from one Access db to another.

I have set up an import routine that loops through db's in a specified
folder, importing selected tables from each to a consolidated db. The
thing is, the code I have below does work to some extent, but only
does so once I have "manually" imported any table from any db in the
folder I am calling on (ie - going through the File -> External Data
-> Import method). It seems to spark it somehow or finds the right
place to look ....but if I dont do this, it appears that the file I am
searching for "cant be found" as such (even though it can still count
how many files are in the folder with the first part of my code), but
once it hits the TransferDatabase part of the code, it just stops.

From other similar posts, the only thing I can glean is that I may
have to set up a Workspace or have an OpenDatabase command first so
the db can be "found"? Strange one that is a bit beyond my
guesstimation as a "non-coder".

Would appreciate any ideas ...the relevant code is posted below...

Private Sub btn_Import_Click()

Dim filename As String
Dim myFileCount As Integer

On Error GoTo Err_Section

filename = Dir("C:\Data\*.mdb") 'want all the Access files in this
dir

'Count the number of Access files in this directory
With Application.FileSearch
..Lookin = "C:\Data"
..SearchSubFolders = False
..filename = "*.mdb"
..Execute
myFileCount = .FoundFiles.Count

' If no files then give msg and get out
If myFileCount = 0 Then
MsgBox "No databases to import", vbInformation, MsgBoxTitle
Exit Sub

'Else away we go, display number of files we are importing in MsgBox
Else
MsgBox "You are about to import " & myFileCount & " databases into the
consolidated Dbase ", vbInformation
End If
End With

' Begin data transfer of Access files
Do While Len(filename) > 0 'start of loop

' Show on user form what file is currently being imported
Me.txt_Status = "Importing tables from " & filename '
Me.Repaint

'''''' Routine stops running here

DoCmd.TransferDatabase acImport, "Microsoft Access", filename, _
acTable, "tbl_EQUIPMENT_H", "tmp_tbl_EQUIPMENT_H"

DoCmd.TransferDatabase acImport, "Microsoft Access", filename, _
acTable, "tbl_EQUIPMENT_D", "tmp_tbl_EQUIPMENT_D"

.....Other stuff..... appending data queries, dropping tmp tables
etc...

filename = Dir 'go to next Access file in loop
Loop

End Sub

Cheers,
Dave G
Melb, Aust

[Allen Browne]

Hi Dave

This should list the tables in another database without OpenDatabase():
SELECT MSysObjects.Name
FROM MSysObjects IN 'C:\Data\MyFile.mdb'
WHERE (MSysObjects.Type = 1) AND NOT
((MSysObjects.Name Like '~*') Or (MSysObjects.Name Like 'MSys*'));

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave Gehrig" <da**********************@hotmail.com> wrote in message
news:92*************************@posting.google.co m...

Hi guys,

Using Access 2003 on XP.

First time caller, long time listener. I am not a programmer per se,
and all my training in coding in the past year or so has come from the
wealth of info in these newsgroups, but I've finally hit a brick wall
re importing tables from one Access db to another.

I have set up an import routine that loops through db's in a specified
folder, importing selected tables from each to a consolidated db. The
thing is, the code I have below does work to some extent, but only
does so once I have "manually" imported any table from any db in the
folder I am calling on (ie - going through the File -> External Data
-> Import method). It seems to spark it somehow or finds the right
place to look ....but if I dont do this, it appears that the file I am
searching for "cant be found" as such (even though it can still count
how many files are in the folder with the first part of my code), but
once it hits the TransferDatabase part of the code, it just stops.

From other similar posts, the only thing I can glean is that I may
have to set up a Workspace or have an OpenDatabase command first so
the db can be "found"? Strange one that is a bit beyond my
guesstimation as a "non-coder".

Would appreciate any ideas ...the relevant code is posted below...

Private Sub btn_Import_Click()

Dim filename As String
Dim myFileCount As Integer

On Error GoTo Err_Section

filename = Dir("C:\Data\*.mdb") 'want all the Access files in this
dir

'Count the number of Access files in this directory
With Application.FileSearch
.Lookin = "C:\Data"
.SearchSubFolders = False
.filename = "*.mdb"
.Execute
myFileCount = .FoundFiles.Count

' If no files then give msg and get out
If myFileCount = 0 Then
MsgBox "No databases to import", vbInformation, MsgBoxTitle
Exit Sub

'Else away we go, display number of files we are importing in MsgBox
Else
MsgBox "You are about to import " & myFileCount & " databases into the
consolidated Dbase ", vbInformation
End If
End With

' Begin data transfer of Access files
Do While Len(filename) > 0 'start of loop

' Show on user form what file is currently being imported
Me.txt_Status = "Importing tables from " & filename '
Me.Repaint

'''''' Routine stops running here

DoCmd.TransferDatabase acImport, "Microsoft Access", filename, _
acTable, "tbl_EQUIPMENT_H", "tmp_tbl_EQUIPMENT_H"

DoCmd.TransferDatabase acImport, "Microsoft Access", filename, _
acTable, "tbl_EQUIPMENT_D", "tmp_tbl_EQUIPMENT_D"

....Other stuff..... appending data queries, dropping tmp tables
etc...

filename = Dir 'go to next Access file in loop
Loop

End Sub

Cheers,
Dave G
Melb, Aust

[Dave Gehrig]

Thanks for the response Allen ...been a fan of your work for a long
time!

Re your suggestion, I'm not exactly sure where I should be putting it
within my code, and what else I need to go with it. Given I only have
to import certain tables from each db that I am looping through, is
"listing" all the tables as you mention below still the way to go
about it?

Might have to walk me through this one mate!

Cheers,
Dave G

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

Hi Dave

This should list the tables in another database without OpenDatabase():
SELECT MSysObjects.Name
FROM MSysObjects IN 'C:\Data\MyFile.mdb'
WHERE (MSysObjects.Type = 1) AND NOT
((MSysObjects.Name Like '~*') Or (MSysObjects.Name Like 'MSys*'));

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

[Allen Browne]

Hi Dave

Okay, re-reading your message, the issue is not that you can't get the
tables in each database, but that they don't import?

Not sure why that would be the case. If the tables are actually there (not
merely attached tables), you should be able to import. Once you've done it
once, do you have an attached table as a result or something?

The idea of the IN clause in the SQL statement may be useful anyway. You may
be able to create an Append query using the IN clause to specify the source
database instead of the TransferDatabase.

You should not have to OpenDatabase. In point of fact, you may get a
performance gain if you are importing lots of tables, but that's just
because Windows is holding the file open instead of opening and closing it;
the process doesn't need the OpenDatabase.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave Gehrig" <da**********************@hotmail.com> wrote in message
news:92**************************@posting.google.c om...

Thanks for the response Allen ...been a fan of your work for a long
time!

Re your suggestion, I'm not exactly sure where I should be putting it
within my code, and what else I need to go with it. Given I only have
to import certain tables from each db that I am looping through, is
"listing" all the tables as you mention below still the way to go
about it?

Might have to walk me through this one mate!

Cheers,
Dave G
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

Hi Dave

This should list the tables in another database without OpenDatabase():
SELECT MSysObjects.Name
FROM MSysObjects IN 'C:\Data\MyFile.mdb'
WHERE (MSysObjects.Type = 1) AND NOT
((MSysObjects.Name Like '~*') Or (MSysObjects.Name Like
'MSys*'));

[Dave Gehrig]

Thanks Allen....

It seems a bit of a strange one, as my code does work (eventually),
but only does so AFTER I have imported a table through the
stock-standard menu File -> Get External Data -> Import method (ie -
if I manually just import any table from one of my d'base's in the
loop folder, I can then go back to the code itself to run the whole
batch from there and it works no problems).

However, if I close my consolidated dbase and re-open it, the routine
again wont run without doing the above first, as it drops out just
before its about to import that first table in the first loop (despite
it still being able to count the number of files in the loop folder!).
Again, if I do the manual import, the code can then be run
successfully.

So I guess the issue is, what is stopping the routine from being able
to import ...but can then trigger itself to work somehow after a
"manual" table import?

I'm gazumped!

Dave

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

Hi Dave

Okay, re-reading your message, the issue is not that you can't get the
tables in each database, but that they don't import?

Not sure why that would be the case. If the tables are actually there (not
merely attached tables), you should be able to import. Once you've done it
once, do you have an attached table as a result or something?

The idea of the IN clause in the SQL statement may be useful anyway. You may
be able to create an Append query using the IN clause to specify the source
database instead of the TransferDatabase.

You should not have to OpenDatabase. In point of fact, you may get a
performance gain if you are importing lots of tables, but that's just
because Windows is holding the file open instead of opening and closing it;
the process doesn't need the OpenDatabase.

[Allen Browne]

Any issues with permissions?

If you want to try the OpenDatabase idea, it's quite simple:
Dim dbData as DAO.Database
Set dbData = OpenDatabase("C:\MyFolder\MyFile.mdb")
'useful stuff in here.
dbData.Close

BTW, how long is the path here? If it's 128 characters or more, that can
have a detrimental effect.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave Gehrig" <da**********************@hotmail.com> wrote in message
news:92**************************@posting.google.c om...

Thanks Allen....

It seems a bit of a strange one, as my code does work (eventually),
but only does so AFTER I have imported a table through the
stock-standard menu File -> Get External Data -> Import method (ie -
if I manually just import any table from one of my d'base's in the
loop folder, I can then go back to the code itself to run the whole
batch from there and it works no problems).

However, if I close my consolidated dbase and re-open it, the routine
again wont run without doing the above first, as it drops out just
before its about to import that first table in the first loop (despite
it still being able to count the number of files in the loop folder!).
Again, if I do the manual import, the code can then be run
successfully.

So I guess the issue is, what is stopping the routine from being able
to import ...but can then trigger itself to work somehow after a
"manual" table import?

I'm gazumped!

Dave
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

Hi Dave

Okay, re-reading your message, the issue is not that you can't get the
tables in each database, but that they don't import?

Not sure why that would be the case. If the tables are actually there
(not
merely attached tables), you should be able to import. Once you've done
it
once, do you have an attached table as a result or something?

The idea of the IN clause in the SQL statement may be useful anyway. You
may
be able to create an Append query using the IN clause to specify the
source
database instead of the TransferDatabase.

You should not have to OpenDatabase. In point of fact, you may get a
performance gain if you are importing lots of tables, but that's just
because Windows is holding the file open instead of opening and closing
it;
the process doesn't need the OpenDatabase.

[Dave Gehrig]

I finally got the thing going, didnt have to resort to OpenDatabase.

Your mentioning of the path name got me thinking to fully specify the
loop folder path in the TransferDatabase line to get it going, eg:

DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Data\" &
filename, _
acTable, "tbl_Equipment_H", "tmp_tbl_Equipment_H"

If I had previously manually imported a table from this folder, I
didnt have to put the "C:\Data\" part in the path to get the thing
going ...was as though the applic had already located the folder it
was looking for, so didnt have to "look" again? ...dunno, still can't
explain that part of it! ...but it's problem solved nonetheless.

For interest sake, the actual path I am calling from in my applic is
longer than 128 char's and it still works ok.

Thanks for your input here Allen.

Cheers,
Dave G
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

Any issues with permissions?

If you want to try the OpenDatabase idea, it's quite simple:
Dim dbData as DAO.Database
Set dbData = OpenDatabase("C:\MyFolder\MyFile.mdb")
'useful stuff in here.
dbData.Close

BTW, how long is the path here? If it's 128 characters or more, that can
have a detrimental effect.