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

brohjoeAsked:
Who is Participating?
 
cyberkiwiCommented:
Hi,

I created a spreadsheet and modified you code to a simple .vbs script.
It works as advertised so I think you must have a the name wrong... can you recheck?

Have a look at the attached.
test.zip
0
 
Patrick MatthewsCommented:
Try replacing:

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


with:

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


BTW, I am puzzled by your condition 0=1.  That is guaranteed to be false all the time, and thus your first query will return no records :)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
brohjoeAuthor Commented:
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.
0
 
brohjoeAuthor Commented:
rs_excel.Open "SELECT * FROM SalesOrders$", conn_excel, adOpenStatic, adLockReadOnly, adCmdTable

returns "Syntax error in FROM clause."
0
 
Rory ArchibaldCommented:
Assuming that is a sheet name:

rs_excel.Open "SELECT * FROM [SalesOrders$]", conn_excel, adOpenStatic, adLockReadOnly, adCmdTable
0
 
Patrick MatthewsCommented:
good catch, Rory :)
0
 
brohjoeAuthor Commented:
Still getting "syntax error in FROM clause" with the brackets around [SalesOrders$] like so.
0
 
cyberkiwiCommented:
Hi,

Try just

rs_excel.Open ("SalesOrders$", conn_excel, adOpenStatic, adLockReadOnly, adCmdTable)
0
 
brohjoeAuthor Commented:
Ok Cyber,

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

and I got "compile error: Syntax error"
0
 
brohjoeAuthor Commented:
Does a recordset object have to be in the form of a sql string?
0
 
brohjoeAuthor Commented:
Or more precisely put, shouldn't it have a Select clause?
0
 
cyberkiwiCommented:
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?
0
 
Rory ArchibaldCommented:
You might try:
rs_excel.Open "SELECT * FROM [SalesOrders$]", conn_excel, adOpenStatic, adLockReadOnly, adCmdText
0
 
brohjoeAuthor Commented:
ok
0
 
brohjoeAuthor Commented:
Run-time error '3265':

item cannot be found in the collection corresponding to the requested name or ordinal.
0
 
brohjoeAuthor Commented:
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.
0
 
brohjoeAuthor Commented:
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.
0
 
brohjoeAuthor Commented:
Note:  I've haven't been working on this consistently for a month...let me clear that up.

:-)
0
 
Rory ArchibaldCommented:
What headers do you have in your table?
0
 
brohjoeAuthor Commented:
Does it make a difference that the Visual Basic IDE is in the same workbook as the data I'm trying to dump?
0
 
brohjoeAuthor Commented:
Headers:  standby
0
 
brohjoeAuthor Commented:
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.
0
 
Rory ArchibaldCommented:
No non-alphanumeric characters in there?
Do you get the same error if you use:
rs_excel.Open "[SalesOrders$]", conn_excel, adOpenStatic, adLockReadOnly, adCmdTable
0
 
brohjoeAuthor Commented:
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.
0
 
brohjoeAuthor Commented:
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.   :-)
0
 
Rory ArchibaldCommented:
Did you change the last argument to adcmdTable?
0
 
brohjoeAuthor Commented:
Actually, I didn't.  Let me change that and see if it makes a difference.
0
 
brohjoeAuthor Commented:
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.

0
 
brohjoeAuthor Commented:
I'm a fair man.  I'll split the points if necessary for valid help here.
0
 
Rory ArchibaldCommented:
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.


0
 
brohjoeAuthor Commented:
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..
0
 
Rory ArchibaldCommented:
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.
0
 
brohjoeAuthor Commented:
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

ErrorMsg.jpg
0
 
brohjoeAuthor Commented:
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.
0
 
brohjoeAuthor Commented:
Dividing up the points and asking a new question based on Cyberkiwi's code since I had been offline for some time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.