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

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").CopyFromRecord set output

MsgBox "Closing Connection..."

dbsConnect.Close

MsgBox "fine"

End Sub

Private Sub CommandButton1_Click()
Call ConnectToDatabase
End Sub

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

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


"Morris" wrote in message
ups.com...
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").CopyFromRecord set output

MsgBox "Closing Connection..."

dbsConnect.Close

MsgBox "fine"

End Sub

Private Sub CommandButton1_Click()
Call ConnectToDatabase
End Sub

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

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

NickHK

"Morris"
groups.com...
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").CopyFromRecord set output

MsgBox "Closing Connection..."

dbsConnect.Close

MsgBox "fine"

End Sub

Private Sub CommandButton1_Click()
Call ConnectToDatabase
End Sub

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

Dim rsData As Recordset

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



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

And now I can use the 'Open' method freely :)

Thanks for your feedback