Excel to SQL to Access

[Alan2005]

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

command.Execute

Next x

Set command = Nothing

 

[SydneyGeek]

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.

Notes:
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
etc

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, _
      Options:=adCmdTable
      
  Application.ScreenUpdating = False
  Application.Goto rSource
  
  'load main project data
  For Rw = 2 To LastRow
    ActiveCell.Offset(1, 0).Select
    rst.AddNew
    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
    rst.Update
  Next Rw
  Application.StatusBar = ""

  rst.Close
  cnn.Close
  
  Application.ScreenUpdating = True
End Sub

Denis

 

[Alan2005]

Thanks Denis, this works very well.
Much appreciated.
Alan