Excel to SQL to Access problem

[Alan2005]

Hi guys,

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.
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

 

[Norie]

Where are you getting the error?

Is it when you run the SQL?

Where are you actually running this code from?

 

[Alan2005]

hi Norie, thanks for replying.
I am getting the error when running the command.execute.
And the code is in a module.
I hope this helps. It is wierd. If I try using just the text fields it goes through without problem.

Thanks

 

[Norie]

Alan

Does the code you posted include the offending fields?

I see you've wrapped all the fields in single quotes, perhaps you don't need them for numeric fields.

 

[Alan2005]

Norie, the code posted uses the first offending field. i.e. Column E or myfield(x,5)
I am testing with just one column at the moment to try and isolate the problem.
I have tried removing the single quotes but still no luck.

Thanks for the try though.