Excel to SQL

[Jani]

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.mdb")
' 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

 

[Jim Thomlinson]

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/index.php?t=envbadac

 

[Rob Hick]

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

 

[MH]

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

 

[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]

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]

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

MH

 

[Jani]

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]

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]

MH - thanks for your help. I was finally successful in getting the code & sql
table set up correctly and now everything works great! jms

 

[OfficeHacker]

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.

 

[Jani]

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