Excel to SQL to Access

Alan2005

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

Forum statistics

Threads
1,078,095
Messages
5,338,203
Members
399,212
Latest member
Braincraft

Some videos you may like

This Week's Hot Topics

Top