Start Free Trial

asked on

Run time error '3709':" The connection cannot be used to perform this operation. "

Hello Experts,

I'm transferring data from Excel on the client to SQL Server in the clouds.
I'm getting the following error on line 11:

Run-time error '3709':
The connection cannot be used to perform this operation. It is either closed or invalid in this context."

I'm sure it's something simple.

Dim conn_excel As New ADODB.Connection
Dim conn_sql As New ADODB.Connection
Dim rs_excel As New ADODB.Recordset
Dim rs_sql As New ADODB.Recordset

Sub loadSQLServer()

conn_sql.Open ("Provider=sqloledb; Data Source=devhybrid2.db.ispProvider.hostedresource.com;Initial Catalog=devhybrid2;Uid=vbaisfun; Password=debug")
conn_excel.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database\XLHybrid.xlsm; Extended Properties=Excel 12.0;")

rs_sql.Open ("SELECT * FROM SalesOrders WHERE 0=1, conn_sql, adOpenDynamic, adLockOptimistic")
rs_excel.Open ("SalesOrders$, conn_excel, adOpenStatic, adLockReadOnly, adCmdTable")

Do While Not rs_excel.EOF
  rs_sql.AddNew
  Dim i As Integer
  For i = 0 To rs_excel.Fields.Count - 1
  rs_sql.Fields(rs_excel.Fields(i).Name).Value = rs_excel.Fields(i).Value
  Next
  rs_sql.Update
  rs_excel.MoveNext
Loop

rs_excel.Close
rs_sql.Close


'Error checking
ErrorExit:
Set rs_sql = Nothing
Set rs_excel = Nothing

Exit Sub

ErrorHandler:
MsgBox Err.Description, vbCritical



End Sub

Open in new window

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

http://www.dbforums.com/delphi-c-etc/1004534-cant-open-connection-run-time-error-3709-a.html

check this thread...

ASKER

I had another expert give me that. I was wondering myself matthewspatrick what that '0 = 1' was, but I was too ashamed to ask. :-)

Ok, I will try your solution.

ASKER

rs_excel.Open "SELECT * FROM SalesOrders$", conn_excel, adOpenStatic, adLockReadOnly, adCmdTable

returns "Syntax error in FROM clause."

Assuming that is a sheet name:

rs_excel.Open "SELECT * FROM [SalesOrders$]", conn_excel, adOpenStatic, adLockReadOnly, adCmdTable

Patrick Matthews

good catch, Rory :)

ASKER

Still getting "syntax error in FROM clause" with the brackets around [SalesOrders$] like so.

Hi,

Try just

rs_excel.Open ("SalesOrders$", conn_excel, adOpenStatic, adLockReadOnly, adCmdTable)

ASKER

Ok Cyber,

I put in rs_excel.Open ("SalesOrders$",conn_excel, adOpenStatic, adLockReadOnly, adCmdTable)

and I got "compile error: Syntax error"

ASKER

Does a recordset object have to be in the form of a sql string?

ASKER

Or more precisely put, shouldn't it have a Select clause?

See here: http://www.devguru.com/technologies/ado/quickref/recordset_open.html

adCmdTable only requires a table name

Probably try it again without the $
Otherwise, do you actually have a sheet by that name in the workbook?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

ok

ASKER

Run-time error '3265':

item cannot be found in the collection corresponding to the requested name or ordinal.

ASKER

cyber,

you have to have the dollar sign when referring to an Excel spreadsheet. I do have a spreadsheet named, 'SalesOrders with 36,920 records.

ASKER

I've been debugging this for over month now. I've downloaded VBA ebooks, searched sources online and I just can't seem to dump this data.

ASKER

Note: I've haven't been working on this consistently for a month...let me clear that up.

:-)

What headers do you have in your table?

ASKER

Does it make a difference that the Visual Basic IDE is in the same workbook as the data I'm trying to dump?

ASKER

Headers: standby

ASKER

I have the following field names:
CustomerName
City
State
Terr
LineNum
ShopNum
ProductNum

et al field names to column X

All of the field names are in the correct database format....CamelCase.

No non-alphanumeric characters in there?
Do you get the same error if you use:
rs_excel.Open "[SalesOrders$]", conn_excel, adOpenStatic, adLockReadOnly, adCmdTable

ASKER

No non-alphanumeric characters. I do have PO_Number, but that's permitted.

I'll try putting the quotations on the outside of the brackets.

ASKER

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

(I knew that wasn't going to work, but in the spirit of desperation....) lol

I'm really taking this with a grain of salt. :-) I'm almost ready to copy and paste it into Access and transfer it that way. But I don't quit. :-)

Did you change the last argument to adcmdTable?

ASKER

Actually, I didn't. Let me change that and see if it makes a difference.

ASKER

rs_excel.Open "SELECT * FROM [SalesOrders$]", conn_excel, adOpenStatic, adLockReadOnly, adCmdTable

Causes "Syntax Error in From clause."

Couple of questions
1. Does the order by which the recordsets are opened matter?
2. Would passing a command object help?
3. Since we are selecting data in Excel and migrating it INTO SQL Server, what about an INSERT clause for the rs_sql recordset?

Comments...anyone feel free here. Don't be shy.

ASKER

I'm a fair man. I'll split the points if necessary for valid help here.

The options are:
rs_excel.Open "[SalesOrders$]", conn_excel, adOpenStatic, adLockReadOnly, adCmdTable
OR:
rs_excel.Open "SELECT * FROM [SalesOrders$]", conn_excel, adOpenStatic, adLockReadOnly, adCmdText
Note the combinations of 1st and 5th arguments! :)
I am interested in whether the former causes the same error as the latter.

ASKER

rs_excel.Open "[SalesOrders$]", conn_excel, adOpenStatic, adLockReadOnly, adCmdTable :

see comment 04/20/10 01:45 PM, ID: 31305191

rs_excel.Open "SELECT * FROM [SalesOrders$]", conn_excel, adOpenStatic, adLockReadOnly, adCmdText

see comment 04/20/10 01:45 PM, ID: 31305191

Different errors. I'm working on a different line of thinking with slightly different code, so if this thread doesn't get resolved, I'll ask a new question with slightly different code using the Command object and the 'INSERT * INTO using the OPENDATASOURCE function..

Those are both the same comment... :)

Using an INSERT statement will be a lot more efficient, but I'm curious now as to why this doesn't work for you.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Ok, I'm back from outer space. (long story)

Anyway...Cyber, thanks for the code. It almost works. First of all, the loadSQLServer function was after the 'End Sub' but that's a simple fix.

I get the following error:

Dim conn_excel 'As New ADODB.Connection
'Dim conn_sql As New ADODB.Connection
Dim rs_excel 'As New ADODB.Recordset
'Dim rs_sql As New ADODB.Recordset

Sub loadSQLServer()

'conn_sql.Open ("Provider=sqloledb; Data Source=devhybrid2.db.ispProvider.hostedresource.com;Initial Catalog=devhybrid2;Uid=vbaisfun; Password=debug")
Set conn_excel = CreateObject("ADODB.Connection")
Set rs_excel = CreateObject("ADODB.Recordset")
conn_excel.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test.xls; Extended Properties=Excel 12.0;")

'rs_sql.Open ("SELECT * FROM SalesOrders WHERE 0=1, conn_sql, adOpenDynamic, adLockOptimistic")
rs_excel.Open "[SalesOrders$]", conn_excel, 3, 1, 2 'adOpenStatic, adLockReadOnly, adCmdTable

Do While Not rs_excel.EOF
'  rs_sql.AddNew
  Dim i 'As Integer
  For i = 0 To rs_excel.Fields.Count - 1
'  rs_sql.Fields(rs_excel.Fields(i).Name).Value = rs_excel.Fields(i).Value
  MsgBox rs_excel.Fields(i).Name & " / " & rs_excel.Fields(i).Value
  Next
'  rs_sql.Update
  rs_excel.MoveNext
Loop

rs_excel.Close
'rs_sql.Close

'Error checking
ErrorExit:
'Set rs_sql = Nothing
Set rs_excel = Nothing

Exit Sub

ErrorHandler:
MsgBox Err.Description, vbCritical

loadSQLServer
End Sub

Open in new window

ASKER

I think what I'll do at this point is award points to everyone, then start all over with Cyber's code and a new question.

ASKER

Dividing up the points and asking a new question based on Cyberkiwi's code since I had been offline for some time.