Method or Data Member Not Found error, after connecting to Microsoft SQL Server

[Morris]

Hello all!

That's the code:

Public dbsConnect As ADODB.Connection

Public Sub ConnectToDatabase()

Const sSOURCE As String = "ConnectToDatabase"

Dim lAttempt As Long
Dim sConnect As String
Dim sSQL As String
Dim rsData As Recordset

sConnect = "Provider=SQLOLEDB;Data Source=ServerName;User
ID=uid;Password=pwd;"

MsgBox "Opening Connection..."

Set dbsConnect = New ADODB.Connection
dbsConnect.ConnectionString = sConnect
dbsConnect.Open

MsgBox "i'm connected!"

-----
Up to this point everything is fine
----

Sheet1.UsedRange.Clear
sSQL = "SHOW TABLES"

Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly,
adCmdText
--------------^^^^^^-----------
This is where I get the error - on rsData.Open
But all the samples and manuals I've found use this method, so where
lies the mistake? I'm using Excel 2003
--------------------------------

'ThisWorkbook.Sheets(1).Range("A1").CopyFromRecordset output

MsgBox "Closing Connection..."

dbsConnect.Close

MsgBox "fine"

End Sub

Private Sub CommandButton1_Click()
Call ConnectToDatabase
End Sub

 

[Robin Hammond]

Morris,

What are you trying to do with SHOW TABLES

I've never seen that one and it won't run in Query Analyser.

Robin Hammond
www.enhanceddatasystems.com

 

[NickHK]

"SHOW TABLES" is a MySQL construct and hence invalid issued in SQL Server.
Check out :
http://www.thescripts.com/forum/thread3724.html

NickHK

 

[Morris]

Dim rsData As Recordset


I've found the solution:
instead of \Dim rsData As Recordsdet\
I have to declare: \Dim rsData As ADODB.Recordsdet\

And now I can use the 'Open' method freely

Thanks for your feedback