Excel to SQL
 

I have code (started from vba code from this site... thanks!!!) where data is
exported from Excel to Access. It works correctly when put into a regular
Access table. When I try to export it to a SQL table, I get a run-time error
3219 - Invalid Operation. The code is below and the problem is on line 8. Can
anyone provide directions on how to export from Excel directly into an SQL
table? Thanks in advance, as always... Jani

Sub ExportProjectToAccess()
' exports data from the GlobalOpsOnly worksheet to a table in an Access
database
' must set references to DAO object in order for macro to run
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\DT\CARMGMT\CapitalExpenditure2.md b")
' open the database
***** Set rs = db.OpenRecordset("dbo_uCARData2", dbOpenTable)
' get all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Sent1") = Range("A" & r).Value
.Fields("Date1") = Range("B" & r).Value
.Fields("Stage") = Range("C" & r).Value
.Fields("Comments1") = Range("D" & r).Value
.Fields("BudgetID") = Range("E" & r).Value
.Fields("CAROriginator") = Range("F" & r).Value
.Fields("CostCenter") = Range("G" & r).Value
.Fields("NPV") = Range("H" & r).Value
.Fields("MIRR") = Range("I" & r).Value
.Fields("AlphaCode") = Range("L" & r).Value
.Fields("Country") = Range("M" & r).Value
.Fields("Description") = Range("O" & r).Value
.Fields("Purpose") = Range("P" & r).Value
.Fields("Category") = Range("Q" & r).Value
.Fields("DateRecd") = Range("R" & r).Value
.Fields("ApprovedBudget") = Range("S" & r).Value
.Fields("CapitalRequested") = Range("T" & r).Value
.Fields("ExpenseRequested") = Range("U" & r).Value
.Fields("Budgeted") = Range("V" & r).Value
.Fields("Funded") = Range("W" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
MsgBox "The job is done!"
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

Excel to SQL
 

Here is a reference site for connection strings. Not much in here about DAO
as it is the old standard but perhaps you could just switch to ADO...

http://www.erlandsendata.no/english/...php?t=envbadac
--
HTH...

Jim Thomlinson


"Jani" wrote:

I have code (started from vba code from this site... thanks!!!) where data is
exported from Excel to Access. It works correctly when put into a regular
Access table. When I try to export it to a SQL table, I get a run-time error
3219 - Invalid Operation. The code is below and the problem is on line 8. Can
anyone provide directions on how to export from Excel directly into an SQL
table? Thanks in advance, as always... Jani

Sub ExportProjectToAccess()
' exports data from the GlobalOpsOnly worksheet to a table in an Access
database
' must set references to DAO object in order for macro to run
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\DT\CARMGMT\CapitalExpenditure2.md b")
' open the database
***** Set rs = db.OpenRecordset("dbo_uCARData2", dbOpenTable)
' get all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Sent1") = Range("A" & r).Value
.Fields("Date1") = Range("B" & r).Value
.Fields("Stage") = Range("C" & r).Value
.Fields("Comments1") = Range("D" & r).Value
.Fields("BudgetID") = Range("E" & r).Value
.Fields("CAROriginator") = Range("F" & r).Value
.Fields("CostCenter") = Range("G" & r).Value
.Fields("NPV") = Range("H" & r).Value
.Fields("MIRR") = Range("I" & r).Value
.Fields("AlphaCode") = Range("L" & r).Value
.Fields("Country") = Range("M" & r).Value
.Fields("Description") = Range("O" & r).Value
.Fields("Purpose") = Range("P" & r).Value
.Fields("Category") = Range("Q" & r).Value
.Fields("DateRecd") = Range("R" & r).Value
.Fields("ApprovedBudget") = Range("S" & r).Value
.Fields("CapitalRequested") = Range("T" & r).Value
.Fields("ExpenseRequested") = Range("U" & r).Value
.Fields("Budgeted") = Range("V" & r).Value
.Fields("Funded") = Range("W" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
MsgBox "The job is done!"
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

Excel to SQL
 

from what you've written, I would guess that you are trying to add data
into a linked table in Access. Linked tables pointing at SQL tables
will generally be non-updatable, definately if you're connecting via
ODBC.

You can test the theory by trying to update the data in the Access
table manually - if you get an error, you have a problem. If you are
able to edit the data then I would guess that the procedure you've
written has a problem with updating linked tables, but I don't know
why...

Rob

Jani wrote:
I have code (started from vba code from this site... thanks!!!) where data is
exported from Excel to Access. It works correctly when put into a regular
Access table. When I try to export it to a SQL table, I get a run-time error
3219 - Invalid Operation. The code is below and the problem is on line 8. Can
anyone provide directions on how to export from Excel directly into an SQL
table? Thanks in advance, as always... Jani

Sub ExportProjectToAccess()
' exports data from the GlobalOpsOnly worksheet to a table in an Access
database
' must set references to DAO object in order for macro to run
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\DT\CARMGMT\CapitalExpenditure2.md b")
' open the database
***** Set rs = db.OpenRecordset("dbo_uCARData2", dbOpenTable)
' get all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Sent1") = Range("A" & r).Value
.Fields("Date1") = Range("B" & r).Value
.Fields("Stage") = Range("C" & r).Value
.Fields("Comments1") = Range("D" & r).Value
.Fields("BudgetID") = Range("E" & r).Value
.Fields("CAROriginator") = Range("F" & r).Value
.Fields("CostCenter") = Range("G" & r).Value
.Fields("NPV") = Range("H" & r).Value
.Fields("MIRR") = Range("I" & r).Value
.Fields("AlphaCode") = Range("L" & r).Value
.Fields("Country") = Range("M" & r).Value
.Fields("Description") = Range("O" & r).Value
.Fields("Purpose") = Range("P" & r).Value
.Fields("Category") = Range("Q" & r).Value
.Fields("DateRecd") = Range("R" & r).Value
.Fields("ApprovedBudget") = Range("S" & r).Value
.Fields("CapitalRequested") = Range("T" & r).Value
.Fields("ExpenseRequested") = Range("U" & r).Value
.Fields("Budgeted") = Range("V" & r).Value
.Fields("Funded") = Range("W" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
MsgBox "The job is done!"
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

Excel to SQL
 

"Rob Hick" wrote in message
ups.com...
from what you've written, I would guess that you are trying to add data
into a linked table in Access. Linked tables pointing at SQL tables
will generally be non-updatable, definately if you're connecting via
ODBC.

Why would you think that a linked table in Access is not updatable?

MH

Excel to SQL
 

I imagine that it's because you are trying to open a linked table as a
table, try:

Set rs = db.OpenRecordset("dbo_uCARData2", dbOpenDynamic)

MH

"Jani" wrote in message
...
I have code (started from vba code from this site... thanks!!!) where data
is
exported from Excel to Access. It works correctly when put into a regular
Access table. When I try to export it to a SQL table, I get a run-time
error
3219 - Invalid Operation. The code is below and the problem is on line 8.
Can
anyone provide directions on how to export from Excel directly into an SQL
table? Thanks in advance, as always... Jani

Sub ExportProjectToAccess()
' exports data from the GlobalOpsOnly worksheet to a table in an Access
database
' must set references to DAO object in order for macro to run
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\DT\CARMGMT\CapitalExpenditure2.md b")
' open the database
***** Set rs = db.OpenRecordset("dbo_uCARData2", dbOpenTable)
' get all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Sent1") = Range("A" & r).Value
.Fields("Date1") = Range("B" & r).Value
.Fields("Stage") = Range("C" & r).Value
.Fields("Comments1") = Range("D" & r).Value
.Fields("BudgetID") = Range("E" & r).Value
.Fields("CAROriginator") = Range("F" & r).Value
.Fields("CostCenter") = Range("G" & r).Value
.Fields("NPV") = Range("H" & r).Value
.Fields("MIRR") = Range("I" & r).Value
.Fields("AlphaCode") = Range("L" & r).Value
.Fields("Country") = Range("M" & r).Value
.Fields("Description") = Range("O" & r).Value
.Fields("Purpose") = Range("P" & r).Value
.Fields("Category") = Range("Q" & r).Value
.Fields("DateRecd") = Range("R" & r).Value
.Fields("ApprovedBudget") = Range("S" & r).Value
.Fields("CapitalRequested") = Range("T" & r).Value
.Fields("ExpenseRequested") = Range("U" & r).Value
.Fields("Budgeted") = Range("V" & r).Value
.Fields("Funded") = Range("W" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
MsgBox "The job is done!"
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

Excel to SQL
 

Thanks for the suggestions... however, the dbOpenDynamic did not work. And, I
can manually add data to the dbo table. Is there a different way that I
should be attempting to move data from Excel to SQL?

"MH" wrote:

I imagine that it's because you are trying to open a linked table as a
table, try:

Set rs = db.OpenRecordset("dbo_uCARData2", dbOpenDynamic)

MH

"Jani" wrote in message
...
I have code (started from vba code from this site... thanks!!!) where data
is
exported from Excel to Access. It works correctly when put into a regular
Access table. When I try to export it to a SQL table, I get a run-time
error
3219 - Invalid Operation. The code is below and the problem is on line 8.
Can
anyone provide directions on how to export from Excel directly into an SQL
table? Thanks in advance, as always... Jani

Sub ExportProjectToAccess()
' exports data from the GlobalOpsOnly worksheet to a table in an Access
database
' must set references to DAO object in order for macro to run
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\DT\CARMGMT\CapitalExpenditure2.md b")
' open the database
***** Set rs = db.OpenRecordset("dbo_uCARData2", dbOpenTable)
' get all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Sent1") = Range("A" & r).Value
.Fields("Date1") = Range("B" & r).Value
.Fields("Stage") = Range("C" & r).Value
.Fields("Comments1") = Range("D" & r).Value
.Fields("BudgetID") = Range("E" & r).Value
.Fields("CAROriginator") = Range("F" & r).Value
.Fields("CostCenter") = Range("G" & r).Value
.Fields("NPV") = Range("H" & r).Value
.Fields("MIRR") = Range("I" & r).Value
.Fields("AlphaCode") = Range("L" & r).Value
.Fields("Country") = Range("M" & r).Value
.Fields("Description") = Range("O" & r).Value
.Fields("Purpose") = Range("P" & r).Value
.Fields("Category") = Range("Q" & r).Value
.Fields("DateRecd") = Range("R" & r).Value
.Fields("ApprovedBudget") = Range("S" & r).Value
.Fields("CapitalRequested") = Range("T" & r).Value
.Fields("ExpenseRequested") = Range("U" & r).Value
.Fields("Budgeted") = Range("V" & r).Value
.Fields("Funded") = Range("W" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
MsgBox "The job is done!"
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

Excel to SQL
 

Possibly, you could use ADO to export the data without using Access as a
go-between.

MH

"Jani" wrote in message
...
Thanks for the suggestions... however, the dbOpenDynamic did not work.
And, I
can manually add data to the dbo table. Is there a different way that I
should be attempting to move data from Excel to SQL?

"MH" wrote:

I imagine that it's because you are trying to open a linked table as a
table, try:

Set rs = db.OpenRecordset("dbo_uCARData2", dbOpenDynamic)

MH

"Jani" wrote in message
...
I have code (started from vba code from this site... thanks!!!) where
data
is
exported from Excel to Access. It works correctly when put into a
regular
Access table. When I try to export it to a SQL table, I get a run-time
error
3219 - Invalid Operation. The code is below and the problem is on line
8.
Can
anyone provide directions on how to export from Excel directly into an
SQL
table? Thanks in advance, as always... Jani

Sub ExportProjectToAccess()
' exports data from the GlobalOpsOnly worksheet to a table in an Access
database
' must set references to DAO object in order for macro to run
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\DT\CARMGMT\CapitalExpenditure2.md b")
' open the database
***** Set rs = db.OpenRecordset("dbo_uCARData2", dbOpenTable)
' get all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Sent1") = Range("A" & r).Value
.Fields("Date1") = Range("B" & r).Value
.Fields("Stage") = Range("C" & r).Value
.Fields("Comments1") = Range("D" & r).Value
.Fields("BudgetID") = Range("E" & r).Value
.Fields("CAROriginator") = Range("F" & r).Value
.Fields("CostCenter") = Range("G" & r).Value
.Fields("NPV") = Range("H" & r).Value
.Fields("MIRR") = Range("I" & r).Value
.Fields("AlphaCode") = Range("L" & r).Value
.Fields("Country") = Range("M" & r).Value
.Fields("Description") = Range("O" & r).Value
.Fields("Purpose") = Range("P" & r).Value
.Fields("Category") = Range("Q" & r).Value
.Fields("DateRecd") = Range("R" & r).Value
.Fields("ApprovedBudget") = Range("S" & r).Value
.Fields("CapitalRequested") = Range("T" & r).Value
.Fields("ExpenseRequested") = Range("U" & r).Value
.Fields("Budgeted") = Range("V" & r).Value
.Fields("Funded") = Range("W" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
MsgBox "The job is done!"
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

Excel to SQL
 

Since I have limited time to get this done I have decided to put the data in
a local access table and then use an Access macro from the Excel macro to
append to SQL. However I can not get that to work either. This code works
without a hitch. Can you provide code how to run an Access macro? Thanks for
all your help - I don't know what I would do without this site. jms

"MH" wrote:

Possibly, you could use ADO to export the data without using Access as a
go-between.

MH

"Jani" wrote in message
...
Thanks for the suggestions... however, the dbOpenDynamic did not work.
And, I
can manually add data to the dbo table. Is there a different way that I
should be attempting to move data from Excel to SQL?

"MH" wrote:

I imagine that it's because you are trying to open a linked table as a
table, try:

Set rs = db.OpenRecordset("dbo_uCARData2", dbOpenDynamic)

MH

"Jani" wrote in message
...
I have code (started from vba code from this site... thanks!!!) where
data
is
exported from Excel to Access. It works correctly when put into a
regular
Access table. When I try to export it to a SQL table, I get a run-time
error
3219 - Invalid Operation. The code is below and the problem is on line
8.
Can
anyone provide directions on how to export from Excel directly into an
SQL
table? Thanks in advance, as always... Jani

Sub ExportProjectToAccess()
' exports data from the GlobalOpsOnly worksheet to a table in an Access
database
' must set references to DAO object in order for macro to run
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\DT\CARMGMT\CapitalExpenditure2.md b")
' open the database
***** Set rs = db.OpenRecordset("dbo_uCARData2", dbOpenTable)
' get all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Sent1") = Range("A" & r).Value
.Fields("Date1") = Range("B" & r).Value
.Fields("Stage") = Range("C" & r).Value
.Fields("Comments1") = Range("D" & r).Value
.Fields("BudgetID") = Range("E" & r).Value
.Fields("CAROriginator") = Range("F" & r).Value
.Fields("CostCenter") = Range("G" & r).Value
.Fields("NPV") = Range("H" & r).Value
.Fields("MIRR") = Range("I" & r).Value
.Fields("AlphaCode") = Range("L" & r).Value
.Fields("Country") = Range("M" & r).Value
.Fields("Description") = Range("O" & r).Value
.Fields("Purpose") = Range("P" & r).Value
.Fields("Category") = Range("Q" & r).Value
.Fields("DateRecd") = Range("R" & r).Value
.Fields("ApprovedBudget") = Range("S" & r).Value
.Fields("CapitalRequested") = Range("T" & r).Value
.Fields("ExpenseRequested") = Range("U" & r).Value
.Fields("Budgeted") = Range("V" & r).Value
.Fields("Funded") = Range("W" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
MsgBox "The job is done!"
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

Excel to SQL
 

It's a long-shot, but have you tried creating a query in Access refering to
your linked table and then using the OpenRecordset method on the query
rather than the table?

Let me know how you get on...

MH


"Jani" wrote in message
...
Since I have limited time to get this done I have decided to put the data
in
a local access table and then use an Access macro from the Excel macro to
append to SQL. However I can not get that to work either. This code works
without a hitch. Can you provide code how to run an Access macro? Thanks
for
all your help - I don't know what I would do without this site. jms

"MH" wrote:

Possibly, you could use ADO to export the data without using Access as a
go-between.

MH

"Jani" wrote in message
...
Thanks for the suggestions... however, the dbOpenDynamic did not work.
And, I
can manually add data to the dbo table. Is there a different way that I
should be attempting to move data from Excel to SQL?

"MH" wrote:

I imagine that it's because you are trying to open a linked table as a
table, try:

Set rs = db.OpenRecordset("dbo_uCARData2", dbOpenDynamic)

MH

"Jani" wrote in message
...
I have code (started from vba code from this site... thanks!!!) where
data
is
exported from Excel to Access. It works correctly when put into a
regular
Access table. When I try to export it to a SQL table, I get a
run-time
error
3219 - Invalid Operation. The code is below and the problem is on
line
8.
Can
anyone provide directions on how to export from Excel directly into
an
SQL
table? Thanks in advance, as always... Jani

Sub ExportProjectToAccess()
' exports data from the GlobalOpsOnly worksheet to a table in an
Access
database
' must set references to DAO object in order for macro to run
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\DT\CARMGMT\CapitalExpenditure2.md b")
' open the database
***** Set rs = db.OpenRecordset("dbo_uCARData2", dbOpenTable)
' get all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Sent1") = Range("A" & r).Value
.Fields("Date1") = Range("B" & r).Value
.Fields("Stage") = Range("C" & r).Value
.Fields("Comments1") = Range("D" & r).Value
.Fields("BudgetID") = Range("E" & r).Value
.Fields("CAROriginator") = Range("F" & r).Value
.Fields("CostCenter") = Range("G" & r).Value
.Fields("NPV") = Range("H" & r).Value
.Fields("MIRR") = Range("I" & r).Value
.Fields("AlphaCode") = Range("L" & r).Value
.Fields("Country") = Range("M" & r).Value
.Fields("Description") = Range("O" & r).Value
.Fields("Purpose") = Range("P" & r).Value
.Fields("Category") = Range("Q" & r).Value
.Fields("DateRecd") = Range("R" & r).Value
.Fields("ApprovedBudget") = Range("S" & r).Value
.Fields("CapitalRequested") = Range("T" & r).Value
.Fields("ExpenseRequested") = Range("U" & r).Value
.Fields("Budgeted") = Range("V" & r).Value
.Fields("Funded") = Range("W" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
MsgBox "The job is done!"
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

Excel to SQL
 

Hi Jani,

Transferring data from Excel to SQL Server via a linked Access table should
work (I've done it before) although I used an Access query (QueryDef object)
rather than opening the recordset.

Some things to look at:
- confirm your privileges on the SQL Server database for the table you are
trying to update. Confirm your account has read/write privileges.

- Don't use the OpenRecordset method on the query. This is an action query
(remember, you're appending). For this use the Execute method instead,
like this:

Dim dibs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryMyAppendQuery")

qdf.Parameters("Sent1") = Range("A" & r).Value
qdf.Parameters("Date1") = Range("B" & r).Value
'etc...

qdf.Execute

- There is no data validation. How do you guarantee that
".Fields("Date1") = Range("B" & r).Value" is being appended with a valid
date value. Passing incorrect data types will certainly generate an error.

Excel to SQL
 

Thanks, OfficeHacker. I have the issue solved - at least in testing it seems
to work fine so I hope I don't run into another glitch! jms

"OfficeHacker" wrote:

Hi Jani,

Transferring data from Excel to SQL Server via a linked Access table should
work (I've done it before) although I used an Access query (QueryDef object)
rather than opening the recordset.

Some things to look at:
- confirm your privileges on the SQL Server database for the table you are
trying to update. Confirm your account has read/write privileges.

- Don't use the OpenRecordset method on the query. This is an action query
(remember, you're appending). For this use the Execute method instead,
like this:

Dim dibs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryMyAppendQuery")

qdf.Parameters("Sent1") = Range("A" & r).Value
qdf.Parameters("Date1") = Range("B" & r).Value
'etc...

qdf.Execute

- There is no data validation. How do you guarantee that
".Fields("Date1") = Range("B" & r).Value" is being appended with a valid
date value. Passing incorrect data types will certainly generate an error.