Import from another database problem

[GirlBlonde]

i am trying to import a database table (via macro) so it is automatically done from a table in another database.

i keep entering the correct path and it is not doing it. it keeps refering that it can not find the file in such and such, however the directory it is quoting me is not what i specified??

i then tried the same path for an excel file and it worked. each time i try to do the database it does not recognise my commands. i have used import spreadsheets in the database before for excel and it keeps refering to that one. i can manually import the file but need it automated.

does anyone know why this is happening????

i have tried keeping it simple with just importing from my h drive
h:/pcs.mdb
but it picks up my directory i have used for previous import of excel documents.

Energy & persistance conquer all things!

 

[dhookom]

Other than using a forward slash rather than a back slash, does your file name include spaces? If so, how are you handling this?

Duane
Hook'D on Access
MS Access MVP

 

[GirlBlonde]

hi duane

no i went to extreme basics the directory is simply h:/pcs.mdb and the table is called table1.

just dont understand....as i said it is really weird because if i change the macro to import a excel document in the same path h:/ it works. very odd.

any suggestions? really appreciate your help

sue

Energy & persistance conquer all things!

 

[ProgramError]

Have you tried as Duane suggests and used a back slash?
I have look at the properties of some of my back eds and their path have a back slash not forward.

h:\pcs.mdb

Ian Mayor (UK)
Program Error
Always make your words short and sweet. Because you never know when you may have to eat them.

 

[GirlBlonde]

hi guys

arrgghh!!!! i tried using the backslash instead and it is still saying it is unable to find the table. it is however now pointing to my correct drive. i have put below my macro just for you all to see. there is only one table in the database i am trying to bring in and i have called it simply table1

transfer type - export
database type - microsoft access
database name - table ( i think this may be wrong)
object type - table
source - h:/pcs.mdb
destination - test
structure only - no

where it says database name do i need to type in the directory again??

thanks so much guys.

Energy & persistance conquer all things!

 

[TheAceMan1]

How are ya GirlBlonde . . .

This Thread Title reads: [purple]Import[/purple] from another database problem . . .

Go back and take a good look at the [blue]TransferType[/blue]:

[tt]transfer type - export - from CurrentDb to ExternalDb
- import - from ExternalDb to CurrentDb[/tt]

If your importing then you should have:

[blue]transfer type - Import
database type - microsoft access
database name - [purple][b]h:/pcs.mdb [/b][/purple]
object type - Table
source - [purple][b]external Db TableName[/b][/purple]
destination - [purple][b]CurrentDb TableName (or new TableName)[/b][/purple]
structure only - No[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[GirlBlonde]

hi aceman

i have never done this before only done excel files so i appologise if i am not quite up there with it.

i changed the specs as you suggested and i keep getting the message 'Not a valid file name'.

how do i ensure it imports table1? i have just called the table it is going to go into test.

really appreciate your help.

sue

Energy & persistance conquer all things!

 

[TheAceMan1]

GirlBlonde . . .

[blue]dhookom[/blue] has already mentioned backslash/forward slash, and here I am doing it!.

[blue]Change: h:[purple][b]/[/b][/purple]pcs.mdb
    To: h:[purple][b]\[/b][/purple]pcs.mdb[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[GirlBlonde]

hi aceman

tried both ways now i get a message saying

'Microsoft Jet database engine could not find the object 'external Db Table1' make sure the object exists and that you spell its name and path correctly'

table1 is the name of the table i want to bring in. why is it not working???

so sorry about this, very frustrating.

Energy & persistance conquer all things!

 

[TheAceMan1]

GirlBlonde . . .

Post as you currently have it! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[dhookom]

I would convert the macro to code and then post the code.

Duane
Hook'D on Access
MS Access MVP

 

[GirlBlonde]

ok here it is as it stands

transfer type - Import
database type - microsoft access
database name - h:/pcs.mdb
object type - Table
source - external Db Table1
destination - CurrentDb test
structure only - No


i have no idea as to how to do via VB

arrgh..

Energy & persistance conquer all things!

 

[dhookom]

All you have to do to convert is to select the macro in the database window, right-click, and save as module.

Why do you keep using a forward slash / when you should be using a back slash \.

Otherwise try
[tt][blue]
transfer type - Import
database type - microsoft access
database name - h:\pcs.mdb
object type - Table
source - Table1
destination - test
structure only - No
[/blue][/tt]

'------------------------------------------------------------
' macImportTable
'
'------------------------------------------------------------
Function macImportTable()
On Error GoTo macImportTable_Err

    DoCmd.TransferDatabase acImport, "Microsoft Access", _
      "h:\pcs.mdb", acTable, "Table1", "test", False

macImportTable_Exit:
    Exit Function

macImportTable_Err:
    MsgBox Error$
    Resume macImportTable_Exit

End Function

Duane
Hook'D on Access
MS Access MVP

 

[GirlBlonde]

hi Dhookom

Yahhoo!!!success with the code. i do not get why the macro didnt work but anyhow same thing isnt it?

(either slash was issuing me with the same message.)

thank you all so very much for your assistance.

one last question, i am soon converting to access 2007 will i get any issues with this code? i just saw 2007 and it freaked me out very scared about converting, hoping that my system will still work as it does now. have armed myself with a 2007 bible to study. any tips?



Energy & persistance conquer all things!

 

[dhookom]

Your macro didn't work because you weren't able to differentiate between suggested "place holder" values and your actual values. For instance in the parameter suggested by Aceman:
[tt]source - [red]external Db [/red]Table1[/tt]
The [tt][red]external Db [/red][/tt] just means for you to enter the table name from the external MDB file.

If you are filling out a form and the instruction is to "Enter your first name on Line A", you would enter "GirlBlonde", not "your first name".


Duane
Hook'D on Access
MS Access MVP