Type Mismatch? Worked yesterday ?

[JamieWarren09]

Hi guys, for some reason my code has stopped working. I get type mismatch on the line below

' Set rs = db.OpenRecordset("agents", dbOpenTable) '

 Dim db As Database
Dim rs As Recordset
Dim ds As Recordset
Dim WS As Workspace


 Set WS = DBEngine.Workspaces(0)
    dbfile = ("\\server\Database\maindb.mdb")
    pwdstring = "jj123"
Set db = DBEngine.OpenDatabase(dbfile, False, False, ";PWD=" & pwdstring)
Set rs = db.OpenRecordset("agents", dbOpenTable)   <<<< This is the line that gets type mismatch

There is defo a table named 'agents' - so unsure why it has stopped working.

Thanks

[Doogle]

My guess would be that the server is unavailable.

[jpbro]

Try fully qualifying your variables when you Dim them. For example if you are using ADO, then declare rs as Dim rs As ADODB.Recordset.

[DataMiser]

Set rs = db.OpenRecordset("agents", dbOpenTable) <<<< This is the line that gets type mismatch

In your other thread you have 's around the table name and you indicated that it works so I can only assume that you literaly have 's in the table name in the db in which case they would be required here as well. Not sure if this would give a type mismatch

Looking at your code you should be getting an error about rs not being set to an instance of on object because you have not used the New keyword

You need to either use it in you Dim statement or your Set statement

[MarkT]

I have never used DAO so I don't know the proper syntax but you don't always have to set an objects as new before using it. The FileSystemObject has a number of examples of this. In this example I'm creating a file object befor setting it as new.

Private Sub Command1_Click()
Dim fso As FileSystemObject
Dim fil As File

    Set fso = New FileSystemObject
    
    If fso.FileExists("c:\boot.ini") Then
        Set fil = fso.GetFile("c:\boot.ini")
        Debug.Print fil.DateCreated
        
        Set fil = Nothing
    End If
    
    Set fso = Nothing
End Sub

[DataMiser]

It has been a long time since I have used DAO so my memory is foggy but in general when you are using an object that requires the use of a set statement you need to use the New keyward in one of the two ways as mentioned either

Dim rs as Recordset
Set rs=New Recordset

or

Dim Rs as New Recordset

Either way will work but if at some point the new keyword is not used then most if not all set statements will fail. Exceptions being if you are using set to map the object to an already existing object such as the recordset property of a data control which would have already issued a call to New internally.

[MarkT]

If that is true of DAO then there is another good reason to switch to ADO. With ADO you an execute commands to set objects.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB"
    cn.Open
    
    sql = "Select ContactName From Customers"
    
    Set rs = cn.Execute(sql)
    
    Debug.Print rs!ContactName
    
    rs.Close
    Set rs = Nothing
    
    cn.Close
    Set cn = Nothing

[DataMiser]

Not much different. In the case above the call to cn.execute would be creating the New instance of the recordset. I think you can do the same thing with DAO though using ADO I would usually use the RS.Open method anyway.