Solved

Import File into Database With VBA (Indexes and Primary key needed)

Posted on 2001-08-29
7
310 Views
Last Modified: 2012-05-04
I have to import a CSV file into a NEW table.
WITH INDEXES ON THE 2 FIRST FIELDS + AUTONUMBER PRIMARY KEY

Structure:
"BBC PRDB","BBC APOLLO","BBC0011 - ZPRDB ORDERS","75","1000"," "," ","22/06/01","094637","000000","22/06/01","094644","0.13","BBC MEDAS","BBC APOLLO BILLING","FTP OTHER STOCKLEY","ECSD-MEDIS","N","22-JUN-2001 9:46:37","186","627","83","169","931108500","13","12"

If I do this with File, Get External Data, Import, and follow the complete wizard.
Everything is imported OK!

TABLE STUCTURE BECOMES THEN
ID        Autonumber Primary Key Indexed No Duplicates
Sender    Text 255 Indexed (Duplicates OK)
Receiver  Text 255 Indexed (Duplicates OK)
...

If I save this file specification (File - Get External Data, Import, Advanced, Create a Spec and Save AS) to be able to use the VBA language to import:
DoCmd.TransferText acImportDelim, "MS Billing Import Specification", _
    "IQ RAW DATA", txtIQBillingFilePath.Value, False
The data is imported OK, but there are NO indexes on sender and receiver.

TABLE STUCTURE BECOMES THEN
ID        Autonumber Primary Key Indexed No Duplicates
Sender    Text 255 Indexed No
Receiver  Text 255 Indexed No

In the import specification I HAVE INDICATED that I want indexes on those 2 fields, but there are none.

Access Version is 97 sr2

Anyone knows why this happens and can think of a solution?

Thanks T
0
Comment
Question by:thimerion
  • 4
  • 2
7 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 200 total points
ID: 6437097
You could add the indexes "manually" by using:

currentdb.execute("CREATE INDEX .......;")

Just check the helpfile for the exact syntax.

Nic;o)
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6439223
For MS help (as nico said)

The next example creates an index consisting of the fields HomePhone and Extension in the Employees table:

CREATE INDEX NewIndex ON Employees (HomePhone, Extension);
0
 
LVL 1

Author Comment

by:thimerion
ID: 6445089
OK Thanks for your help!
I use now the code:
DoCmd.TransferText acImportDelim, "MS Billing Import Specification", _
    "MsRawData", txtMSBillingFilePath.Value, False
CurrentDb.Execute "CREATE INDEX MSRD_Sender_idx ON MsRawData (Sender);"
CurrentDb.Execute "CREATE INDEX MSRD_Receiver_idx ON MsRawData (Receiver);"
CurrentDb.Execute "CREATE INDEX MSRD_Customer_idx ON MsRawData (Customer);"
CurrentDb.Execute "CREATE INDEX MSRD_DateAndTime_idx ON MsRawData (DateAndTime);"

To get the desired result.

However I have my doubts on the performance of this solution, as it takes quite a while to create indexes on the existing data.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:thimerion
ID: 6445104
Thanks!
But if you can find a more performant (quicker) way to import a 20Mb text file and add indexes, I'd be happy to hear it.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6445114
The creation of an index always asks time, as Access will have to build a file having all key's in it.
It's only necessary to build indexes when the appication is also using them.
To detect this you'll need to check the execution plans of the queries.

Personally I always start without extra indexes and when performance is growing bad, I'll add them.

Nic;o)
0
 
LVL 1

Author Comment

by:thimerion
ID: 6445165
I am definately using those indexes;
I run about 40 queries on that table with those indexed fields in the WHERE clause.

I was just wondering:

What is the most performant solution?
1*/Create empty table WITH indexes,
then add the data.
2*/Add the data, then create indexes.

Why is the import specification not doing it's work correctly?
0
 
LVL 1

Author Comment

by:thimerion
ID: 6445304
I just tested it myself...

Insert the data first,
then create the indexes takes access 1'20''

Create a table with indexes,
then insert the data takes access 1'35''

Anyway thanks again Nico
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now