ADODB Import fixedwidth - no value given for required parameters

[ajaynon]

Hi,

First of all let me preface this with: I've tried hard to find the answer to this myself, so I'm sorry if this already exists on the forum.

I'm trying to do a mass import into access using the OLEDB driver and "fixed width" if you notice in the below code, the commented out part is what I was using and this worked fine, but I want to avoid using a drop table command due to PEBKAC incase the table is dropped and excel randomly crashes, the user in this instance does not have access, and will not be able to recreate the table.

I've tried all kinds of combinations in my INSERT command and have played with the schema file to no avail. It gets stuck trying to execute the SQL saying "no value given for required parameters". I know there is something wrong with my syntax or schema file, I just don't know what.

Sample data also provided. Thanks in advance.

    Dim cn As ADODB.Connection ', Rs As ADODB.Recordset, r As Long
    Set cn = New ADODB.Connection
    cn.Mode = 3 ' adModeReadWrite
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & ActiveWorkbook.Path & "\base.mdb" '& ";Extended Properties=""text;HDR=NO;FMT=FixedLength"""
    cn.Execute "DELETE * FROM BASE;"
    SQLstring = "INSERT INTO [BASE] ([ignore], [MSN], [Service Status], [Product Plan], [Add Date], [Status Change], [Dealer Code], [Account Number]) SELECT F1, F2, F3, F4, F5, F6, F7, F8 FROM [Text;HDR=NO;DATABASE=" & ActiveWorkbook.Path & "].[BASE.TXT]"
    cn.Execute SQLstring

    'cn.Execute "DROP TABLE BASE;"
    'SQLstring = "SELECT * INTO [BASE] FROM [Text;DATABASE=" & ActiveWorkbook.Path & "].[BASE.TXT]"
    'cn.Execute SQLstring
    
    cn.Execute "DELETE * FROM  [BASE] WHERE BASE.[Account Number] Is Null or BASE.[Account Number] = 'C.ACCOUNT_NUMBER'"
    cn.Close
    Set cn = Nothing

Schema.ini contains:

[BASE.TXT]
ColNameHeader=False
MaxScanRows=0
CharacterSet=ANSI
Format=FixedLength
col1=ignore Text Width 1
col2=MSN Text Width 20
col3="Service Status" Text Width 19
col4="Product Plan" Text Width 22
col5="Add Date" DateTime Width 13
col6="Status Change" DateTime Width 23
col7="Dealer Code" Text Width 16
col8="Account Number" Text Width 18

Sample data in text file

 P.CELLULAR_NUMBER   C.SERVICE_STATUS   P.PRODUCT_PLAN_CODE   C.ADD_DATE   C.STATUS_CHANGE_DATE   C.DEALER_CODE   C.ACCOUNT_NUMBER  
 0999999999          A                  DOWTV1                 6/07/11      6/07/11               RS999           99999999          
 0999999999          C                  DOWTV1                30/03/11     20/12/11               RS999           99999999          
 0999999999          C                  DOWTV1                28/09/11     29/09/11               RS999           99999999          
 0999999999          A                  DOWTV1                10/03/11     10/03/11               RS999           99999999          
 0999999999          A                  DOWTV1                 4/08/11      4/08/11               RS999           99999999          
 0999999999          C                  DOWTV1                 8/06/11      7/11/11               RS999           99999999          
 0999999999          C                  DOWTV1                28/09/11     11/10/11               RS999           99999999