docmd.transferdatabase

[joaompc]

Hello guys!

Need help please!

I have a database, that is open, in this db I import with VBA several .dbf file to a local folder. Now I want to transfer this .dbf to my database.

I was using the docmd.transferdatabase, but the problem is that for each .dfb a new table is created in the main db, and what I want is to make just one table and append all data in that table.

All the .dbf have the same columns and field names.

I saw that is possible to do with docmd.runSQL Insert ....

Let me know your ideas

thank you

 

[pwbrown]

I saw that is possible to do with docmd.runSQL Insert ....

That's just code to run a query.

Personally I'd rather create my own query in access then use DoCmd.OpenQuery "...." to open / run it rather than inserting the sql in the code.

 

[joaompc]

Yes I know!

But I think it's possible to do:

docmd.runSQL "Insert Into table In "c\:.....dbf" SELECT * FROM .dbf"

i don't no..

 

[pwbrown]

Yes I know!

But I think it's possible to do:

docmd.runSQL "Insert Into table In "c\:.....dbf" SELECT * FROM .dbf"

i don't no..

Are you using a form and want to click a button to run this?
If not just create an append query and run it.
Need more details on what you're trying to do.

 

[joaompc]

Thank you,

What I want to do is this:

I have a database A with a table called SSM.

On my c:\temp\db I have 10 files .dbf that have one table each. The tables are equal, the only thing that changes is the number of records. But the number of fields are the same.

My table SSM on db A is equal to the .dbf tables.

I want to import those .dbf tables to my db A. But When I the docmd.transferdatabase it works but it creates 10 tables with the .dbf tables. I want to copy all of them to SSM table. Append!

Thank you

 

[pwbrown]

The SQL will be something like:

INSERT INTO SSM ( Field1, Field2, Field3 )
SELECT [New table].Field1, [New table].Field2, [New table].Field3
FROM [New table];

Change the new table name and field names to match your own.

Easier if you let access create the sql for you:

create > query design > select new table> close > drag the fields from the new table down to the bottom> click append (under design tab) > select your SSM table> click ok > select run> check it worked > delete the old table > your done!

 

[joaompc]

Thank you

But is it possible to do that when the [New Table] is on another database?

 

[pwbrown]

Thank you

But is it possible to do that when the [New Table] is on another database?

Now I'm confused, I thought it was on the same db after it got imported?

 

[joaompc]

Yes, I import the 10 .dbf and then run the append query to add them to the SSM table.

I was wondering if it is possible to do the query without importing the .dbf

 

[pwbrown]

I'm not sure, maybe someone more experienced would know.
I'm pretty sure you can though.