Excel to SQL to Access problem

Alan2005

New Member
Joined
Oct 12, 2005
Messages
22
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
Where are you getting the error?

Is it when you run the SQL?

Where are you actually running this code from?
 

Alan2005

New Member
Joined
Oct 12, 2005
Messages
22
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
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

New Member
Joined
Oct 12, 2005
Messages
22
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.
 

Forum statistics

Threads
1,078,401
Messages
5,339,998
Members
399,347
Latest member
chlearning

Some videos you may like

This Week's Hot Topics

Top