Transferring data from Excel to Access

    In VBA Excel transferring data from Excel to Access.


    I want to know how to increment the [ID] for each value of the range. [ID] is the Primary Key.


    I have tried "NULL".


    I have tried adding "n=n+1" after the "for each" and "thisSQL = "INSERT INTO... VALUES (" & n & ",..."


    Code
    For Each value In Range("D5:D" & Cells(Rows.Count, "D").End(xlUp).Row)      accDateSub = Cells(value.Row, 1)      accDescSub = Cells(value.Row, 2)      accSub = Cells(value.Row, 3)      thisSQL = "INSERT INTO [Table1] ([ID], [Date], [Desc], [Data]) VALUES ("[U]What goes here?[/U] ", #" & Format(CDate(accDate), "MM/DD/YY") & "#, '" & accDesc & "', " & accData & ");"      conn.Execute CommandText:=thisSQLNext

    Re: Transferring data from Excel to Access


    If ID is the Primary Key, then hopefully it is an Autonumber field; if that's correct, you don't need to add data for that field, the DB engine will do it.


    If it is a sequential number you're maintaining yourself then you need to seed it with trhe next number to use and increment that each time around the loop... something like:


    [code]


    Dim lID as Long


    lID = 34567 '// Or whatever


    For Each value In Range("D5:D" ...
    '// Other Code
    thisSQL = "INSERT INTO [Table1] ([ID], [Date], [Desc], [Data]) VALUES (lID, #" & Format... '// If it is really a number then there are no " delimiting it in the SQL string
    lID = lID +1
    '// Other code
    Next

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login