Advanced CSV Import Procedure using VB and SQL within Access 2007

[Constantine AI]

I am wanting to import CSV files into Access, which isn't a problem at the moment the code i have is as follows:

Dim strSQL As String
Dim CSVTable As String
Dim FilePath As String
Dim Result As String
 
strSQL = "DELETE * from csvordlin"
CSVTable = "csvordlin"
 
FilePath = InputBox("Please Enter a Path File for the CSV Location!", "Criteria Required")
If FilePath = "" Then
Else
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
DoCmd.TransferText acImportDelim, , CSVTable, FilePath, False
Result = MsgBox("You have Successfully Imported your chosen CSV file!", vbInformation, "Import Status")
End If

This successfully imports details into csvordlin table. Attributes such as StkID, Qty, Description, Height, Width and Depth are contained within. I am wanting to transfer these details into an existing table called "ordlin" (OrderLine) for an particular order but it needs check parameters such as if a certain StkID does not exist within the "stkmas" table then provide the option to ADD these Details to the "stkmas" table with an INSERT statement.

I have created a Customer Order Form where there is an OrderHeader and OrderLine contained within. I have OrderNo set as the Primary Key for the OrderHeader and OrderNo and StkID for the OrderLine so i can have many lines for one OrderHead.

So as a Step-by-Step approach, i am wanting the following to happen:

1. Create the OrderHead
2. Click on a Button to Import the CSV file as shown above.
3. Check the Imported Data exists within existing Stk file.
4. If not have the option to add to the existing Stk file.
5. Import the data into the Form OrderLine where it will have to assign an OrderNo with each row of data.
6. Assign correct Stk ID's or (New ones) with the Stk Descriptions imported. (Will explain below **).
7. Assign any Price that belongs to the existing "stkmas" table. (D-LOOKUP most likely)

This is my crappy attempt of transferring the data from the csvordlin table to the ordlin table:

Dim strSQL As String
 
strSQL = "INSERT INTO ordlin(StkID,Qty,Width,Depth,Height)SELECT F1,F4,F5,F6,F7 FROM csvordlin"
 
DoCmd.RunSQL strSQL

It didn't work as u may know. F1 = StkID, F2 Stk Description, F4 = Qty, F5 = Width, F6 = Depth and F7 = Height.

** When sorting this database out i assigned StkID's to Stock Items, where as the Imported data uses the StkDesc as there IDs, so i am gonna have to match my StkShortDesc with there StkID as they dont use numbers. For example;

StkID = 133, StkShortDesc = Base, Qty = 1 - Mine
StkID = Base, StkDesc = Base Board, Qty = 1 - CSV File (Theirs)

My Visula Basic is limited when trying to do something like this and is a bit out of my league as you could probably tell. Any help would be greatly appreciated.

Thanks in advance!

[nico5038]

A rather daunting task.
To start with the SQLrun I would run the string first from the query-editor. Simply paste the string in the SQL-mode and press the ! for running the statement.
I guess that one of the fields datatype doesn't match,but Access will warn you for he proper error.

Nic;o

[Stewart Ross]

Constantine Al, this thread is a virtual duplicate of this thread which you posted. I have retained your former thread as it contains a screenshot which the current thread does not, but I have closed it to prevent confusion. Please do not double-post your threads in future - you will not find they are answered any faster as a result. It is also against our posting guidelines, with which you should be familiar. The guidelines are listed at the top of the forum.

MODERATOR