Excel to SQL to Access


New Member
Oct 12, 2005
Hi guys, I posted this on the Excel forum but thought I'd give it a go here as well.

I am having difficulty with a SQL string and upload to an Access table.
I have 6 fields, A-D are text, E-F are numbers. When uploading via a SQL statement into Access I am getting a runtime error. However, if I remove the columns E-F, all works fine. I have formatted the Access Table to accept numbers, and also tried it with text but still no luck. It just doesn't like it.
Now I am confused. On top of this if I do a straight import external data from the menu bars all works without incident.
Please help, my colleague is getting frustrated with my swearing.
I am running from a module and the error occurs at command.execute.
Thanks - Code I'm using is below;
Dim mysql As String
Dim nrow As Integer
Dim myfield1() As Variant

Dim mysheet As Worksheet

Set mysheet = Sheets("Sheet1")
nrow = mysheet.Range("a1000").End(xlUp).Row

mysheet.Range("a1", Cells(nrow, 6)).Name = "toaccess1"

myfield1 = mysheet.Range("toaccess1").Value

ReDim myfield1(LBound(myfield1) To UBound(myfield1), 1 To 5)

For x = LBound(myfield1) To UBound(myfield1)
For y = 1 To 5
myfield1(x, y) = mysheet.Cells(x + 1, y).Value
Next y

mysql = "INSERT INTO Backups(Client,Policy,Schedule,Media,Size) "
mysql = mysql + "VALUES ('" & myfield1(x, 1) & "','" & myfield1(x, 2) & "','" & myfield1(x, 3) & _
"','" & myfield1(x, 4) & "','" & myfield1(x, 5) & "') "

command.CommandText = mysql


Next x

Set command = Nothing


MrExcel MVP
Aug 5, 2003
Your problem is that the rows need to be added one at a time. You can run an Update query on the whole recordset, or use teh CopyFromRecordset method to retrieve records from Access in one hit, but you need to loop through each row when you load new records.

Below is a routine that I have used successfully.

1. You will need to set a reference to Microsoft ActiveX Data Objects 2.x library (2.5 or higher) if you have no already done so
2. I created a 2-column table for defining the field names and their course column. The table is called DB_Fields and the columns are (1) the field name in the Access table and (2) the column number in the Excel worksheet. So, you'd have --
Field 1    1
Field 2    2
Field 3    3
The reasons I did this were (a) to simplify maintenance if additional fields were added and (b) to enable a loop for loading the fields
3. Change sheet and table names to suit.

Code --
Sub PushDataToMDB()
  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim sPath As String
  Dim rSource As Range
  Dim MyConn
  Dim Rw As Long, LastRow As Long
  Dim sVersion As String
  Dim varFields() As Variant 'array holds fields for upload to tblProjectData
  Dim i As Integer
  'load field names and column locations into arrays
  varFields = Range("DB_Fields")
  'initialise variables
  Set rSource = Sheets("DB Upload").Range("A1").CurrentRegion 'change sheet name to suit
  sPath = "Put your full path to the database in here" 
  MyConn = sPath
  Set cnn = New ADODB.Connection
  With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open MyConn
  End With
  Set rst = New ADODB.Recordset
  rst.CursorLocation = adUseServer
  'change table name to suit
  rst.Open Source:="tblProjectData", ActiveConnection:=cnn, _
    CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
  Application.ScreenUpdating = False
  Application.Goto rSource
  'load main project data
  For Rw = 2 To LastRow
    ActiveCell.Offset(1, 0).Select
    Application.StatusBar = "Adding Record " & Rw - 1 & " of " & LastRow - 1
    For i = 2 To UBound(varFields)
        rst(varFields(i, 1)) = Cells(ActiveCell.Row, varFields(i, 2))
    Next i
  Next Rw
  Application.StatusBar = ""

  Application.ScreenUpdating = True
End Sub

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics