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

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back