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 & ",..."

    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:


    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

Participate now!

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