method to Scan tables in one directory

S

souris

I am looking for a easy way to scan all the dbf tables in one directory and
import them in to my SQL server database using MS Access as front end
application,

Any information is great appreciated,


Souris,
 
J

John Vinson

I am looking for a easy way to scan all the dbf tables in one directory and
import them in to my SQL server database using MS Access as front end
application,

Any information is great appreciated,


Souris,
Click to expand...

Take a look at the VBA help for the Dir() function. You should be able
to use Dir() to find all the .dbf files in a directory and step
through them.

John W. Vinson[MVP]
 
S

souris

Thanks for the information,

I use Dir() function to scan all the files.
I need copy the data structure to my SQL server database, because the
structure is unknown.
I tried 'DoCmd.CopyObejct', 'DoCmd.CopyDatabaseFile' and
'DoCmd.TransferDatabase', None of them works.
Do you have any idea which command that I can copy the database structure?

Thanks again,

Souris,
 
J

John Vinson

Thanks for the information,

I use Dir() function to scan all the files.
I need copy the data structure to my SQL server database, because the
structure is unknown.
I tried 'DoCmd.CopyObejct', 'DoCmd.CopyDatabaseFile' and
'DoCmd.TransferDatabase', None of them works.
Do you have any idea which command that I can copy the database structure?
Click to expand...

If you have a linked dBase file (which you can do with
TransferDatabase) and linked SQL tables (likewise or they can be
permanently linked), then you can base an Append query upon the dBase
file and append the data into the SQL table.

John W. Vinson[MVP]
 
S

souris

Because I do not know the structure of the table, I can not have a
permanently linked SQL table.
I have to create a SQL table base on the table structure.

May I use
'DoCmd.CopyObject' to copy my dbf table structure to my SQL server?

Thanks again for the information,

Souris,
 
J

John Vinson

Because I do not know the structure of the table, I can not have a
permanently linked SQL table.
I have to create a SQL table base on the table structure.
Click to expand...

Hrm. I don't know if a MakeTable will work - probably not.

You may need to use DAO or ADO to loop through the linked dBase
table's Fields collection and construct a SQL DDL query. Given the
fact that you have three different dialogs of SQL and table
conventions involved this may be tricky!


John W. Vinson[MVP]
 
Top