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

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

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

"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

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

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

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

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

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