Importing CSV files in a new to be created Access database

[Guus2005]

Hi,

I need to import 2 csv files which increase my database to almost 500MB. A primary key needs to be added to both tables and an index.
After that i connect the tables to currentdb, perform a few checks als disconnect the tables and drop the database.

So i've come up with this solution:
I want to create a new database and after that is finished i want to import a csv file in that new database.
Creating a new database is easy enough but when i import the csv file in that new database it gives an error message that the import specification can't be found.
Apparently it uses the IMEX specifications stored in the new database and obviously they are not there.

Dim app As Access.Application
    
Set app = New Access.Application
app.OpenCurrentDatabase strDatabasename

' import the csv file into that database
app.DoCmd.TransferText acImportDelim, strImportSpecification, strTablename, strFilename, blnHasFieldNames

How do i export my IMEX specs to the new database?

Thanks for your time!

 

[Gasman]

Search here. Isladogs has a routine that allows that, plus amending.?

 

[Guus2005]

Isladogs has a lot about IMEX specs but copying it to another database is not among them.

Perhaps i can use a database template in which the specifications are incorporated and copy that database to a new name.

 

[bastanu]

Have you seen this thread?

Move text file import specification from one database to another

I have some carefully curated text file import specifications, such as I would refer to in an argument of TransferText, in one database. I need to move them to another database. Is this possible to do successfully...... I tried clicking the Options button in the External Data import wizard...

www.access-programmers.co.uk

 

[Guus2005]

Yep, doesn't give a specific answer to my question.
However in the code i saw this little piece of code

CurrentProject.ImportExportSpecifications.Item(OldName)

When you see .Item, usually .Add is also available. And yes, there is!
I need to try something like this:

RemoteProject.ImportExportSpecifications.Add OldName, CurrentProject.ImportExportSpecifications.Item(OldName).XML

Thanks for your answer!

 

[theDBguy]

Yep, doesn't give a specific answer to my question.
However in the code i saw this little piece of code

CurrentProject.ImportExportSpecifications.Item(OldName)

When you see .Item, usually .Add is also available. And yes, there is!
I need to try something like this:

RemoteProject.ImportExportSpecifications.Add OldName, CurrentProject.ImportExportSpecifications.Item(OldName).XML

Thanks for your answer!

Hi. I hope you get it to work. But if not, perhaps another approach you could try is to link the text files in your current db where you have the specs stored. Then, you can transfer the data to the new db after you create it.

Just a thought...

 

[isladogs]

Perhaps I'm missing the point but I'm unclear why you want to do it like this,
Instead, why not LINK to the two CSV files to your current dB (rather than IMPORT them), then append the data into your final table with the PK and indices.

Alternatively you can create the table externally, import it into your current database making sure you tick the option to include the Import/Export specs. The IMEX specification XML will be transferred along with the table.

 

[Guus2005]

i don't want to bloat my frontend with a temporary table.
I can link the csv file to my frontend but then i can't add a primary key. (Not sure if i can set an index on a linked table)
In one of the two csv files i need a PK to be used as a linenumber.

First csv is a reference, the second is the one that needs to be checked.
I loop through the second, record by record and i can have a counter running along. Then i don't need a primary key.
The indexes speed things up but i don't really *need* them.

So here's my solution:
Link the csv files and make some adjustments in the code.

Thank you for your thoughts!

 

[bastanu]

You actually can add a primary key to a linked table using DDL in VBA, I used to do it when linking SQL Server views in VBA.

Cheers,

 

[Pat Hartman]

I think he Imex tables are MSys tables that you can actually update so just export them from your FE after you create the temp BE.