129
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!
