Importing CSV files in a new to be created Access database (1 Viewer)

Guus2005

AWF VIP
Local time
Today, 12:20
Joined
Jun 26, 2007
Messages
2,645
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.
Code:
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

Enthusiastic Amateur
Local time
Today, 11:20
Joined
Sep 21, 2011
Messages
14,038
Search here. Isladogs has a routine that allows that, plus amending.?
 

Guus2005

AWF VIP
Local time
Today, 12:20
Joined
Jun 26, 2007
Messages
2,645
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

AWF VIP
Local time
Today, 04:20
Joined
Apr 13, 2010
Messages
1,401
Have you seen this thread?
 

Guus2005

AWF VIP
Local time
Today, 12:20
Joined
Jun 26, 2007
Messages
2,645
Yep, doesn't give a specific answer to my question.
However in the code i saw this little piece of code

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:
Code:
RemoteProject.ImportExportSpecifications.Add OldName, CurrentProject.ImportExportSpecifications.Item(OldName).XML

Thanks for your answer!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:20
Joined
Oct 29, 2018
Messages
21,357
Yep, doesn't give a specific answer to my question.
However in the code i saw this little piece of code

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:
Code:
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

MVP / VIP
Local time
Today, 11:20
Joined
Jan 14, 2017
Messages
18,186
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

AWF VIP
Local time
Today, 12:20
Joined
Jun 26, 2007
Messages
2,645
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

AWF VIP
Local time
Today, 04:20
Joined
Apr 13, 2010
Messages
1,401
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

Super Moderator
Staff member
Local time
Today, 07:20
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom