Invalid Object Name 1

[smil3y]

After reading many of the articles in this forum I attempted to create an ADO connection to a database attached to MSDE2000A.

I am using VB6 and have referenced ADO 2.8 library.

The using the SQLOLEDB.1 provider, the connection opens but when I actually execute the line:
.Open sSQL, Con, adOpenStatic, adLockBatchOptimistic, adCmdText

I get an invalid Object Name "Employee".

I have also created a DSN connection via ODBC and using the MSDASQL.1 provider the connection opens and I can read the records. When I set up the DSN connection I also use NT authentication which I am sure is similar to SSPI.

The code used follows and includes the 2 connection strings that I use:

Can anyone offer some advice please.

Option Explicit

Dim Con As ADODB.Connection
Dim Rec As ADODB.Recordset
Dim sConn As String
Dim sSQL As String

Private Sub btnRecords_Click()
Set Con = New ADODB.Connection

Con.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;" & _
"Data Source=(Local);" & _
"Initial Catalogue=RMJR Personnel Management System;" & _
"Integrated Security=SSPI"

'Con.ConnectionString = "Provider=MSDASQL.1;" & _
"Persist Security Info=False;" & _
"Data Source=RMJR Personnel Management System;" & _
"Initial Catalog=RMJR Personnel Management System"

Con.Open
Debug.Print Con

sSQL = "SELECT * FROM Employee"

Set Rec = New ADODB.Recordset
If Con.State = adStateOpen Then
With Rec
.CursorLocation = adUseClient
.Open sSQL, Con, adOpenStatic, adLockBatchOptimistic, adCmdText

MsgBox Rec.RecordCount
Set Rec = Nothing
End With
Else
Con.Close
MsgBox "The database connection failed!", , "Connection Failure"

End If
Con.Close
Set Con = Nothing
End Sub

 

[gmmastros]

Change: Initial Catalogue

To: Initial Catalog

For more information regarding connection strings:

http://www.connectionstrings.com

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

For further explanation....

Since Initial Catalog[!]ue[/!] isn't a recognized property for a connection string, it is ignored. Therefore, when you open your connection to the database, you are actually opening the default database for the user, which is probably Master (a system database that you should never mess with). This explains why it couldn't find the employee table, since that table does not exist in the master database.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[smil3y]

Many thanks.
You definitly can not mix US with Australian English.

 

[BobRodes]

Oh, we do it all the time. For example, while we often write "catalog" we usually write "dialogue" and "technique" rather than "dialog" and "technic".

Like most government intiatives, Teddy Roosevelt's initiative to "modernize" spellings only went halfway.