I found this code that will copy the data from excel and export it to a
database that i created. Unfor, it only takes goes rows. So when i hit the
upload button, it only takes the firs row. I am wondering if anyone can help
me out to make it loop until last record
I tried the loop command but it was coming back as error. Please help. thanks

code:
Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=F:\System Analyst\Logistics\Logistics CDI.mdb" ' 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 = "INSERT INTO [test] " _
& "Values ('" & Range("A12").Value & "', '" & Range("B12").Value & "',
'" _
& Range("C12").Value & "', '" & Range("D12").Value & "', '" &
Range("E12").Value & "', '" _
& Range("F12").Value & "', '" & Range("G12").Value & "', '" &
Range("H12").Value & "', '" _
& Range("I12").Value & "', '" & Range("J12").Value & "', '" &
Range("K12").Value & "', '" _
& Range("L12").Value & "', '" & Range("M12").Value & "', '" &
Range("N12").Value & "')"
' 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

MyCn.Execute SQLStr

MsgBox "Data has been uploaded to CDI ERROR DATA"
MyCn.Close
Set MyCn = Nothing


End Sub

Untested but try:

Sub loadData()
Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim i As Long
Dim r As Long

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=F:\System Analyst\Logistics\Logistics CDI.mdb"

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

For r = 12 To i '<<change start row
SQLStr = "INSERT INTO [test] " _
& "Values ('" & Range("A" & r).Value & "', '" _
& Range("B" & r).Value & "', '" _
& Range("C" & r).Value & "', '" & Range("D" & r).Value _
& "', '" & Range("E" & r).Value & "', '" _
& Range("F" & r).Value & "', '" & Range("G" & r).Value _
& "', '" & Range("H" & r).Value & "', '" _
& Range("I" & r).Value & "', '" & Range("J" & r).Value _
& "', '" & Range("K" & r).Value & "', '" _
& Range("L" & r).Value & "', '" & Range("M" & r).Value _
& "', '" & Range("N" & r).Value & "')"

MyCn.Execute SQLStr
Next r

MsgBox "Data has been uploaded to CDI ERROR DATA"
MyCn.Close
Set MyCn = Nothing

End Sub

Hope this helps
Rowan

Justin wrote:
I found this code that will copy the data from excel and export it to a
database that i created. Unfor, it only takes goes rows. So when i hit the
upload button, it only takes the firs row. I am wondering if anyone can help
me out to make it loop until last record
I tried the loop command but it was coming back as error. Please help. thanks

code:
Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=F:\System Analyst\Logistics\Logistics CDI.mdb" ' 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 = "INSERT INTO [test] " _
& "Values ('" & Range("A12").Value & "', '" & Range("B12").Value & "',
'" _
& Range("C12").Value & "', '" & Range("D12").Value & "', '" &
Range("E12").Value & "', '" _
& Range("F12").Value & "', '" & Range("G12").Value & "', '" &
Range("H12").Value & "', '" _
& Range("I12").Value & "', '" & Range("J12").Value & "', '" &
Range("K12").Value & "', '" _
& Range("L12").Value & "', '" & Range("M12").Value & "', '" &
Range("N12").Value & "')"
' 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

MyCn.Execute SQLStr

MsgBox "Data has been uploaded to CDI ERROR DATA"
MyCn.Close
Set MyCn = Nothing


End Sub

yes it works, thanks

"Rowan Drummond" wrote:

Untested but try:

Sub loadData()
Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim i As Long
Dim r As Long

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=F:\System Analyst\Logistics\Logistics CDI.mdb"

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

For r = 12 To i '<<change start row
SQLStr = "INSERT INTO [test] " _
& "Values ('" & Range("A" & r).Value & "', '" _
& Range("B" & r).Value & "', '" _
& Range("C" & r).Value & "', '" & Range("D" & r).Value _
& "', '" & Range("E" & r).Value & "', '" _
& Range("F" & r).Value & "', '" & Range("G" & r).Value _
& "', '" & Range("H" & r).Value & "', '" _
& Range("I" & r).Value & "', '" & Range("J" & r).Value _
& "', '" & Range("K" & r).Value & "', '" _
& Range("L" & r).Value & "', '" & Range("M" & r).Value _
& "', '" & Range("N" & r).Value & "')"

MyCn.Execute SQLStr
Next r

MsgBox "Data has been uploaded to CDI ERROR DATA"
MyCn.Close
Set MyCn = Nothing

End Sub

Hope this helps
Rowan

Justin wrote:
I found this code that will copy the data from excel and export it to a
database that i created. Unfor, it only takes goes rows. So when i hit the
upload button, it only takes the firs row. I am wondering if anyone can help
me out to make it loop until last record
I tried the loop command but it was coming back as error. Please help. thanks

code:
Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=F:\System Analyst\Logistics\Logistics CDI.mdb" ' 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 = "INSERT INTO [test] " _
& "Values ('" & Range("A12").Value & "', '" & Range("B12").Value & "',
'" _
& Range("C12").Value & "', '" & Range("D12").Value & "', '" &
Range("E12").Value & "', '" _
& Range("F12").Value & "', '" & Range("G12").Value & "', '" &
Range("H12").Value & "', '" _
& Range("I12").Value & "', '" & Range("J12").Value & "', '" &
Range("K12").Value & "', '" _
& Range("L12").Value & "', '" & Range("M12").Value & "', '" &
Range("N12").Value & "')"
' 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

MyCn.Execute SQLStr

MsgBox "Data has been uploaded to CDI ERROR DATA"
MyCn.Close
Set MyCn = Nothing


End Sub

You're welcome.

Justin wrote:
yes it works, thanks

I have another question, it has to do with the exporting to excel. Now the
code that you modify actually acts like a module for another excel sheet
(template sheet) where I import data from access. Now on this template sheet,
there is an upload button, which will call for the code (the one you helped
out with) and export all teh data and soo on

Now on this template sheet, there is a drop down where the user will pick 5
things
I need to have it that once the user press upload, that comment field will
lock so that they can not change it. Reason is that this 1 sheet is on a
share drive and 3 other people will work it. Now i wish that they can work it
in a day but sometimes 1 person will work 1 day and anotehr another and the
last a week later. The director wants to upload it even if all the work isn't
complete, upload whatever is done, and when the others are completed, upload
that data. I already know that it will upload 2wice, that is where the access
database will determine if this is a duplicate or not, if it is, then delete,
if not, save.
I don't want to 1 user to change that comment field a day later when i
upload it again. That is why i need to know a way to look a field where it
has a comment(there is 5 comments) and lock it once the user press upload.
thanks

Hi Justin

I would need to know more about the comment field in order to help you.
It is not clear if this is a cell on the template sheet (perhaps linked
to the dropdown) or maybe the dropdown itself or maybe something
completely different. So:
1) Is the commment field an excel cell and if so what is it's address or
is it more than one cell we are talking about.
2) If it is not a field what is it eg Forms ComboBox, Controls Toolbox
listbox, cell comment etc
3) How is the field updated by the user before the load to access occurs.
4) Is the template sheet protected at all.

Regards
Rowan

Justin wrote:
I have another question, it has to do with the exporting to excel. Now the
code that you modify actually acts like a module for another excel sheet
(template sheet) where I import data from access. Now on this template sheet,
there is an upload button, which will call for the code (the one you helped
out with) and export all teh data and soo on

Now on this template sheet, there is a drop down where the user will pick 5
things
I need to have it that once the user press upload, that comment field will
lock so that they can not change it. Reason is that this 1 sheet is on a
share drive and 3 other people will work it. Now i wish that they can work it
in a day but sometimes 1 person will work 1 day and anotehr another and the
last a week later. The director wants to upload it even if all the work isn't
complete, upload whatever is done, and when the others are completed, upload
that data. I already know that it will upload 2wice, that is where the access
database will determine if this is a duplicate or not, if it is, then delete,
if not, save.
I don't want to 1 user to change that comment field a day later when i
upload it again. That is why i need to know a way to look a field where it
has a comment(there is 5 comments) and lock it once the user press upload.
thanks