Syntax error in INSERT INTO statement

G

Guest

I've been looking through this code all day and can't figure out where the
syntax is. Can anyone see it?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim i As Long
Dim r As Long
Dim delRows As Range
Dim mRow As Long

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "DELETE * FROM 0106Int;"
MyCn.Execute SQLStr

i = Cells(Rows.Count, 1).End(xlUp).Row

'from row 10, or whatever you want to start
For mRow = 10 To ActiveSheet.UsedRange.Rows.Count

'make sure there is data in that row by testing a colum you know will
have data
If Len(Cells(mRow, 10)) > 0 Then

For r = 10 To i '<<Change Start Row
SQLStr = "INSERT INTO [0106Int] (Date, Branch, [WO#], Customer, PartNumber,
MaterialType, Size, Quantity, UnitOfMeasure, Cell, Operator, DiscrepancyCode,
Discrepancy, DispositionCode, Cost)" _
& " SELECT '" & (Cells(mRow, 1)) & "','" & (Cells(mRow, 2)) & "','" &
(Cells(mRow, 3)) & "','" & (Cells(mRow, 4)) _
& "','" & (Cells(mRow, 5)) & "','" & (Cells(mRow, 6)) & "','" &
(Cells(mRow, 7)) & "','" & (Cells(mRow, 8)) _
& "','" & (Cells(mRow, 9)) & "','" & (Cells(mRow, 10)) & "','" &
(Cells(mRow, 11)) & "','" & (Cells(mRow, 12)) _
& "','" & (Cells(mRow, 13)) & "','" & (Cells(mRow, 14)) & "','" &
(Cells(mRow, 15)) & "';"

' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

Next r

Debug.Print SQLStr

MyCn.Execute SQLStr

End If

Next mRow

If delRows Is Nothing Then
Set delRows = Range("A" & r)
Else
Set delRows = Union(delRows, Range("A" & r))
End If

delRows.EntireRow.Delete

MsgBox "Data has been uploaded to 0106Int"
MyCn.Close
Set MyCn = Nothing

End Sub
 
R

Ron Hinds

Look at the table 0106Int in Design view. Check the Type of each field. If
it isn't text, remove the single quote character (') on either side of the
corresponding cell in the SELECT list. If it is date or time, replace the
single quote characters with the pound symbol (#).

Secret Squirrel said:
I've been looking through this code all day and can't figure out where the
syntax is. Can anyone see it?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim i As Long
Dim r As Long
Dim delRows As Range
Dim mRow As Long

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Click to expand...
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Qualit
y-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "DELETE * FROM 0106Int;"
MyCn.Execute SQLStr

i = Cells(Rows.Count, 1).End(xlUp).Row

'from row 10, or whatever you want to start
For mRow = 10 To ActiveSheet.UsedRange.Rows.Count

'make sure there is data in that row by testing a colum you know will
have data
If Len(Cells(mRow, 10)) > 0 Then

For r = 10 To i '<<Change Start Row
SQLStr = "INSERT INTO [0106Int] (Date, Branch, [WO#], Customer, PartNumber,
MaterialType, Size, Quantity, UnitOfMeasure, Cell, Operator, DiscrepancyCode,
Discrepancy, DispositionCode, Cost)" _
& " SELECT '" & (Cells(mRow, 1)) & "','" & (Cells(mRow, 2)) & "','" &
(Cells(mRow, 3)) & "','" & (Cells(mRow, 4)) _
& "','" & (Cells(mRow, 5)) & "','" & (Cells(mRow, 6)) & "','" &
(Cells(mRow, 7)) & "','" & (Cells(mRow, 8)) _
& "','" & (Cells(mRow, 9)) & "','" & (Cells(mRow, 10)) & "','" &
(Cells(mRow, 11)) & "','" & (Cells(mRow, 12)) _
& "','" & (Cells(mRow, 13)) & "','" & (Cells(mRow, 14)) & "','" &
(Cells(mRow, 15)) & "';"

' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

Next r

Debug.Print SQLStr

MyCn.Execute SQLStr

End If

Next mRow

If delRows Is Nothing Then
Set delRows = Range("A" & r)
Else
Set delRows = Union(delRows, Range("A" & r))
End If

delRows.EntireRow.Delete

MsgBox "Data has been uploaded to 0106Int"
MyCn.Close
Set MyCn = Nothing

End Sub
Click to expand...
 
R

Ron Hinds

Also, you are missing the FROM portion of your SELECT.

Secret Squirrel said:
I've been looking through this code all day and can't figure out where the
syntax is. Can anyone see it?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim i As Long
Dim r As Long
Dim delRows As Range
Dim mRow As Long

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Click to expand...
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Qualit
y-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "DELETE * FROM 0106Int;"
MyCn.Execute SQLStr

i = Cells(Rows.Count, 1).End(xlUp).Row

'from row 10, or whatever you want to start
For mRow = 10 To ActiveSheet.UsedRange.Rows.Count

'make sure there is data in that row by testing a colum you know will
have data
If Len(Cells(mRow, 10)) > 0 Then

For r = 10 To i '<<Change Start Row
SQLStr = "INSERT INTO [0106Int] (Date, Branch, [WO#], Customer, PartNumber,
MaterialType, Size, Quantity, UnitOfMeasure, Cell, Operator, DiscrepancyCode,
Discrepancy, DispositionCode, Cost)" _
& " SELECT '" & (Cells(mRow, 1)) & "','" & (Cells(mRow, 2)) & "','" &
(Cells(mRow, 3)) & "','" & (Cells(mRow, 4)) _
& "','" & (Cells(mRow, 5)) & "','" & (Cells(mRow, 6)) & "','" &
(Cells(mRow, 7)) & "','" & (Cells(mRow, 8)) _
& "','" & (Cells(mRow, 9)) & "','" & (Cells(mRow, 10)) & "','" &
(Cells(mRow, 11)) & "','" & (Cells(mRow, 12)) _
& "','" & (Cells(mRow, 13)) & "','" & (Cells(mRow, 14)) & "','" &
(Cells(mRow, 15)) & "';"

' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

Next r

Debug.Print SQLStr

MyCn.Execute SQLStr

End If

Next mRow

If delRows Is Nothing Then
Set delRows = Range("A" & r)
Else
Set delRows = Union(delRows, Range("A" & r))
End If

delRows.EntireRow.Delete

MsgBox "Data has been uploaded to 0106Int"
MyCn.Close
Set MyCn = Nothing

End Sub
Click to expand...
 
D

Douglas J. Steele

You can only use the INSERT INTO...SELECT syntax if you're actually
selecting values from a table.

You appear to be trying to insert values from Cells (whatever that is...),
in which case you need to use the INSERT INTO...VALUES syntax.

Look up the details of the "INSERT INTO Statement" in the Help file.
 
G

Guest

I cut and pasted the SQL into a query in access and tried to run it to find
out where the error was. It hightlighted the word "Date" in my statement. I
just retyped it and now everything works fine. Not sure what happened there
but it seems to work ok now. As for your comments well now I'm confused. If
it's working ok should I still modify it or will it be ok? What I'm doing is
uploading data from excel to a table in access.

Douglas J. Steele said:
You can only use the INSERT INTO...SELECT syntax if you're actually
selecting values from a table.

You appear to be trying to insert values from Cells (whatever that is...),
in which case you need to use the INSERT INTO...VALUES syntax.

Look up the details of the "INSERT INTO Statement" in the Help file.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Secret Squirrel said:
I've been looking through this code all day and can't figure out where the
syntax is. Can anyone see it?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim i As Long
Dim r As Long
Dim delRows As Range
Dim mRow As Long

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\Sys\sys.mdw;" & _
"Uid=admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here
also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "DELETE * FROM 0106Int;"
MyCn.Execute SQLStr

i = Cells(Rows.Count, 1).End(xlUp).Row

'from row 10, or whatever you want to start
For mRow = 10 To ActiveSheet.UsedRange.Rows.Count

'make sure there is data in that row by testing a colum you know will
have data
If Len(Cells(mRow, 10)) > 0 Then

For r = 10 To i '<<Change Start Row
SQLStr = "INSERT INTO [0106Int] (Date, Branch, [WO#], Customer,
PartNumber,
MaterialType, Size, Quantity, UnitOfMeasure, Cell, Operator,
DiscrepancyCode,
Discrepancy, DispositionCode, Cost)" _
& " SELECT '" & (Cells(mRow, 1)) & "','" & (Cells(mRow, 2)) & "','" &
(Cells(mRow, 3)) & "','" & (Cells(mRow, 4)) _
& "','" & (Cells(mRow, 5)) & "','" & (Cells(mRow, 6)) & "','" &
(Cells(mRow, 7)) & "','" & (Cells(mRow, 8)) _
& "','" & (Cells(mRow, 9)) & "','" & (Cells(mRow, 10)) & "','" &
(Cells(mRow, 11)) & "','" & (Cells(mRow, 12)) _
& "','" & (Cells(mRow, 13)) & "','" & (Cells(mRow, 14)) & "','" &
(Cells(mRow, 15)) & "';"

' NOTE: The above assumes all fields are TEXT data type, that is why the
"'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

Next r

Debug.Print SQLStr

MyCn.Execute SQLStr

End If

Next mRow

If delRows Is Nothing Then
Set delRows = Range("A" & r)
Else
Set delRows = Union(delRows, Range("A" & r))
End If

delRows.EntireRow.Delete

MsgBox "Data has been uploaded to 0106Int"
MyCn.Close
Set MyCn = Nothing

End Sub
Click to expand...
Click to expand...
 
G

Guest

Hi Ron,
It's working ok now. For some reason the word "Date" was causing the
problem. I just retyped it and now it's fine. That was kind of weird.
Anyways, if it's working ok do I still need the FROM portion of the SELECT?

Ron Hinds said:
Also, you are missing the FROM portion of your SELECT.

Secret Squirrel said:
I've been looking through this code all day and can't figure out where the
syntax is. Can anyone see it?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim i As Long
Dim r As Long
Dim delRows As Range
Dim mRow As Long

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Click to expand...
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Qualit
y-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "DELETE * FROM 0106Int;"
MyCn.Execute SQLStr

i = Cells(Rows.Count, 1).End(xlUp).Row

'from row 10, or whatever you want to start
For mRow = 10 To ActiveSheet.UsedRange.Rows.Count

'make sure there is data in that row by testing a colum you know will
have data
If Len(Cells(mRow, 10)) > 0 Then

For r = 10 To i '<<Change Start Row
SQLStr = "INSERT INTO [0106Int] (Date, Branch, [WO#], Customer, PartNumber,
MaterialType, Size, Quantity, UnitOfMeasure, Cell, Operator, DiscrepancyCode,
Discrepancy, DispositionCode, Cost)" _
& " SELECT '" & (Cells(mRow, 1)) & "','" & (Cells(mRow, 2)) & "','" &
(Cells(mRow, 3)) & "','" & (Cells(mRow, 4)) _
& "','" & (Cells(mRow, 5)) & "','" & (Cells(mRow, 6)) & "','" &
(Cells(mRow, 7)) & "','" & (Cells(mRow, 8)) _
& "','" & (Cells(mRow, 9)) & "','" & (Cells(mRow, 10)) & "','" &
(Cells(mRow, 11)) & "','" & (Cells(mRow, 12)) _
& "','" & (Cells(mRow, 13)) & "','" & (Cells(mRow, 14)) & "','" &
(Cells(mRow, 15)) & "';"

' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

Next r

Debug.Print SQLStr

MyCn.Execute SQLStr

End If

Next mRow

If delRows Is Nothing Then
Set delRows = Range("A" & r)
Else
Set delRows = Union(delRows, Range("A" & r))
End If

delRows.EntireRow.Delete

MsgBox "Data has been uploaded to 0106Int"
MyCn.Close
Set MyCn = Nothing

End Sub
Click to expand...
Click to expand...
 
R

Ron Hinds

Date is a keyword in Access, that must be why. What did you do - put
brackets [] around it? Anyway, if it is working then you don't need FROM.

Secret Squirrel said:
Hi Ron,
It's working ok now. For some reason the word "Date" was causing the
problem. I just retyped it and now it's fine. That was kind of weird.
Anyways, if it's working ok do I still need the FROM portion of the SELECT?

Ron Hinds said:
Also, you are missing the FROM portion of your SELECT.

Secret Squirrel said:
I've been looking through this code all day and can't figure out where the
syntax is. Can anyone see it?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim i As Long
Dim r As Long
Dim delRows As Range
Dim mRow As Long

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Click to expand...
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Qualit
y-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "DELETE * FROM 0106Int;"
MyCn.Execute SQLStr

i = Cells(Rows.Count, 1).End(xlUp).Row

'from row 10, or whatever you want to start
For mRow = 10 To ActiveSheet.UsedRange.Rows.Count

'make sure there is data in that row by testing a colum you know will
have data
If Len(Cells(mRow, 10)) > 0 Then

For r = 10 To i '<<Change Start Row
SQLStr = "INSERT INTO [0106Int] (Date, Branch, [WO#], Customer, PartNumber,
MaterialType, Size, Quantity, UnitOfMeasure, Cell, Operator, DiscrepancyCode,
Discrepancy, DispositionCode, Cost)" _
& " SELECT '" & (Cells(mRow, 1)) & "','" & (Cells(mRow, 2)) & "','" &
(Cells(mRow, 3)) & "','" & (Cells(mRow, 4)) _
& "','" & (Cells(mRow, 5)) & "','" & (Cells(mRow, 6)) & "','" &
(Cells(mRow, 7)) & "','" & (Cells(mRow, 8)) _
& "','" & (Cells(mRow, 9)) & "','" & (Cells(mRow, 10)) & "','" &
(Cells(mRow, 11)) & "','" & (Cells(mRow, 12)) _
& "','" & (Cells(mRow, 13)) & "','" & (Cells(mRow, 14)) & "','" &
(Cells(mRow, 15)) & "';"

' NOTE: The above assumes all fields are TEXT data type, that is why
Click to expand...
the
"'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

Next r

Debug.Print SQLStr

MyCn.Execute SQLStr

End If

Next mRow

If delRows Is Nothing Then
Set delRows = Range("A" & r)
Else
Set delRows = Union(delRows, Range("A" & r))
End If

delRows.EntireRow.Delete

MsgBox "Data has been uploaded to 0106Int"
MyCn.Close
Set MyCn = Nothing

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

No I just changed it from "Date" to "DateCreated". I didn't realize I could
use the brackets for that. I thought it was only for special characters in a
field name. What would putting the FROM in there do anyways?

Ron Hinds said:
Date is a keyword in Access, that must be why. What did you do - put
brackets [] around it? Anyway, if it is working then you don't need FROM.

Secret Squirrel said:
Hi Ron,
It's working ok now. For some reason the word "Date" was causing the
problem. I just retyped it and now it's fine. That was kind of weird.
Anyways, if it's working ok do I still need the FROM portion of the SELECT?

Ron Hinds said:
Also, you are missing the FROM portion of your SELECT.

message I've been looking through this code all day and can't figure out where the
syntax is. Can anyone see it?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim i As Long
Dim r As Long
Dim delRows As Range
Dim mRow As Long

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _

"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action

Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Qualit
y-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here
also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "DELETE * FROM 0106Int;"
MyCn.Execute SQLStr

i = Cells(Rows.Count, 1).End(xlUp).Row

'from row 10, or whatever you want to start
For mRow = 10 To ActiveSheet.UsedRange.Rows.Count

'make sure there is data in that row by testing a colum you know will
have data
If Len(Cells(mRow, 10)) > 0 Then

For r = 10 To i '<<Change Start Row
SQLStr = "INSERT INTO [0106Int] (Date, Branch, [WO#], Customer,
PartNumber,
MaterialType, Size, Quantity, UnitOfMeasure, Cell, Operator,
DiscrepancyCode,
Discrepancy, DispositionCode, Cost)" _
& " SELECT '" & (Cells(mRow, 1)) & "','" & (Cells(mRow, 2)) & "','" &
(Cells(mRow, 3)) & "','" & (Cells(mRow, 4)) _
& "','" & (Cells(mRow, 5)) & "','" & (Cells(mRow, 6)) & "','" &
(Cells(mRow, 7)) & "','" & (Cells(mRow, 8)) _
& "','" & (Cells(mRow, 9)) & "','" & (Cells(mRow, 10)) & "','" &
(Cells(mRow, 11)) & "','" & (Cells(mRow, 12)) _
& "','" & (Cells(mRow, 13)) & "','" & (Cells(mRow, 14)) & "','" &
(Cells(mRow, 15)) & "';"

' NOTE: The above assumes all fields are TEXT data type, that is why the
"'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

Next r

Debug.Print SQLStr

MyCn.Execute SQLStr

End If

Next mRow

If delRows Is Nothing Then
Set delRows = Range("A" & r)
Else
Set delRows = Union(delRows, Range("A" & r))
End If

delRows.EntireRow.Delete

MsgBox "Data has been uploaded to 0106Int"
MyCn.Close
Set MyCn = Nothing

End Sub
Click to expand...
Click to expand...
Click to expand...
 
R

Ron Hinds

In a sesne, *any* reserved word would be just like a special character,
hence the need for brackets. Normally, FROM would be followed by a table
name, query name, etc. I didn't realize your query was running against Excel
data.

Secret Squirrel said:
No I just changed it from "Date" to "DateCreated". I didn't realize I could
use the brackets for that. I thought it was only for special characters in a
field name. What would putting the FROM in there do anyways?

Ron Hinds said:
Date is a keyword in Access, that must be why. What did you do - put
brackets [] around it? Anyway, if it is working then you don't need FROM.

Secret Squirrel said:
Hi Ron,
It's working ok now. For some reason the word "Date" was causing the
problem. I just retyped it and now it's fine. That was kind of weird.
Anyways, if it's working ok do I still need the FROM portion of the SELECT?

:

Also, you are missing the FROM portion of your SELECT.

message I've been looking through this code all day and can't figure out
Click to expand...
where
the
syntax is. Can anyone see it?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim i As Long
Dim r As Long
Dim delRows As Range
Dim mRow As Long

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _

"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Click to expand...
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Qualit
y-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here
also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "DELETE * FROM 0106Int;"
MyCn.Execute SQLStr

i = Cells(Rows.Count, 1).End(xlUp).Row

'from row 10, or whatever you want to start
For mRow = 10 To ActiveSheet.UsedRange.Rows.Count

'make sure there is data in that row by testing a colum you
Click to expand...
know
will
have data
If Len(Cells(mRow, 10)) > 0 Then

For r = 10 To i '<<Change Start Row
SQLStr = "INSERT INTO [0106Int] (Date, Branch, [WO#], Customer,
PartNumber,
MaterialType, Size, Quantity, UnitOfMeasure, Cell, Operator,
DiscrepancyCode,
Discrepancy, DispositionCode, Cost)" _
& " SELECT '" & (Cells(mRow, 1)) & "','" & (Cells(mRow, 2)) &
Click to expand...
"','"
&
(Cells(mRow, 3)) & "','" & (Cells(mRow, 4)) _
& "','" & (Cells(mRow, 5)) & "','" & (Cells(mRow, 6)) & "','" &
(Cells(mRow, 7)) & "','" & (Cells(mRow, 8)) _
& "','" & (Cells(mRow, 9)) & "','" & (Cells(mRow, 10)) & "','" &
(Cells(mRow, 11)) & "','" & (Cells(mRow, 12)) _
& "','" & (Cells(mRow, 13)) & "','" & (Cells(mRow, 14)) & "','" &
(Cells(mRow, 15)) & "';"

' NOTE: The above assumes all fields are TEXT data type, that is
Click to expand...
why
the
"'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

Next r

Debug.Print SQLStr

MyCn.Execute SQLStr

End If

Next mRow

If delRows Is Nothing Then
Set delRows = Range("A" & r)
Else
Set delRows = Union(delRows, Range("A" & r))
End If

delRows.EntireRow.Delete

MsgBox "Data has been uploaded to 0106Int"
MyCn.Close
Set MyCn = Nothing

End Sub
Click to expand...
Click to expand...
Click to expand...
 
V

Van T. Dinh

Have you checked Access Help (Microsoft JET Reference section) on "INSERT
INTO .. " as Doug advised?

There are 2 distinct syntaxes for INSERT INTO:

* INSERT INTO ... VALUES (list of values)

* INSERT INTO ... SELECT (list of Fields) FROM (sources)

Your INSERT INTO ... statement does not have the FROM cluase that is
necessary with the SELECT Clause.

Doug thought (and I agree) that you are providing the list of values and you
don't have the FROM clause, you need to use the first syntax with the
keyword VALUES, not SELECT.
 
D

Douglas J. Steele

Maybe ADO lets you use SELECT statements without a WHERE clause (IIRC, you
can do that in SQL Server).

However, I still think Secret Squirrel should use the first syntax if he/she
isn't going to include the FROM clause.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data from Excel to Access 4
Data from Excel to Access Table 12
Excel export to Access - Run-time error 1004 6
ODBC Run-Time Error Handling Help 5
exporting to excel 21
Inserting Excel Data Range into Access Table 2
For/Next loop to process INSERT INTO statement 4
SQL Syntax Error in Query 2

Top