PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Course Of The Month
4 days, 22 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Import File into Database With VBA (Indexes and Primary key needed)
Posted on 2001-08-29
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","000
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
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
WITH INDEXES ON THE 2 FIRST FIELDS + AUTONUMBER PRIMARY KEY
Structure:
"BBC PRDB","BBC APOLLO","BBC0011 - ZPRDB ORDERS","75","1000"," "," ","22/06/01","094637","000
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
-
2
7 Comments
You could add the indexes "manually" by using:
currentdb.execute("CREATE INDEX .......;")
Just check the helpfile for the exact syntax.
Nic;o)
currentdb.execute("CREATE INDEX .......;")
Just check the helpfile for the exact syntax.
Nic;o)
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);
The next example creates an index consisting of the fields HomePhone and Extension in the Employees table:
CREATE INDEX NewIndex ON Employees (HomePhone, Extension);
OK Thanks for your help!
I use now the code:
DoCmd.TransferText acImportDelim, "MS Billing Import Specification", _
"MsRawData", txtMSBillingFilePath.Value
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.
I use now the code:
DoCmd.TransferText acImportDelim, "MS Billing Import Specification", _
"MsRawData", txtMSBillingFilePath.Value
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.
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.
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.
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)
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)
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?
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?
Featured Post
For Any Use & On Any Platform
Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database. This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another.
Base the dependent combo on a query for its row source:
Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses
Course of the Month4 days, 22 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.