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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.