How to Make a Connection in DAO 2

[kriehn16]

I am writing an application in which the user's frontend is in Microsoft Outlook 2003 and the backend is in Access '97. From my understanding, Access '97 uses DAO and Outlook 2003 uses ADO so when I am trying to make a recordset and connection I have been using DAO. However when I try to run my code I get the error message "Method or Data Member Not Found" and it highlights ".Connection" from the line "Set cn = DAO.Connection." I have posted my code for making a connection below. I would appreciate any help anyone would be able to give me.


stdbName = "C:\Programming\SelfServeTestSchedule.mdb"

Dim stSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine.Workspaces(0).Databases(0)

Dim cn As DAO.Connection
Set cn = DAO.Connection
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open stdbName

stSQL1 = "SELECT initials FROM UserDataTable"
Set rs = db.OpenRecordset(stSQL1, dbOpenDynaset)

-Eric

 

[PWise]

set a reference to microsoft DAO x.xx libary and use dao syntex

 

[kriehn16]

I have a reference set up to a DAO library already. I guess my problem then is that I do not know the DAO syntax.

 

[PWise]

try

Dim stSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
stdbName = "C:\Programming\SelfServeTestSchedule.mdb"
Set db= Workspaces(0).OpenDatabase (stdbName, options, read-only, connect)
stSQL1 = "SELECT initials FROM UserDataTable"
Set rs = db.OpenRecordset(stSQL1, dbOpenDynaset)

 

[kriehn16]

I changed my code to what is posted above but now I get Run-Time Error '13' Type Mismatch. I checked my references and I have the DAO reference listed as a higher priority than the ActiveX Data Objects Library so I dont think that is the problem. Any Ideas?

 

[PWise]

on what line do get the error

 

[kriehn16]

I am not sure what line it is on because it doesnt tell me. I have the rest of my code posted below. What I am trying to do is to have a drop down list on a form where the list comes from a column in a table. I think the problem might be in the rest of my code.


Sub UserForm_Initialize()
Dim stSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
stdbName = "H:\Access\SelfServeTestSchedule.mdb"
Set db = Workspaces(0).OpenDatabase(stdbName)
stSQL1 = "SELECT initials FROM UserDataTable"
Set rs = db.OpenRecordset(stSQL1, dbOpenDynaset)

Dim num_user As Integer

Dim i As Integer
num_users = rs.RecordCount
Dim init() As String
init = rs.GetRows

For i = 0 To num_users - 1
With ComboBox2
.AddItem init(i)
End With

Next i


rs.Close
End Sub

Thanks for your help so far

 

[PWise]

try for simpler code

Sub UserForm_Initialize()
    Dim stSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    stdbName = "H:\Access\SelfServeTestSchedule.mdb"
    Set db = Workspaces(0).OpenDatabase(stdbName)
    stSQL1 = "SELECT initials FROM UserDataTable"
    Set rs = db.OpenRecordset(stSQL1, dbOpenDynaset)

    do while not rs.eof
        ComboBox2.AddItem rs!initials         
    loop
    rs.Close
End Sub

 

[Remou]

Is H:\Access\SelfServeTestSchedule.mdb the current database or is UserDataTable a linked table?

 

[kriehn16]

I think UserDataTable is a linked table. I am programming in VBA from Microsoft Outlook 2003 but I need to get my information from tables that are in Access 97. That path is to an Access 97 file and UserDataTable is a table within that file.

 

[JoeAtWork]

FYI - you can use ADO to connect to an Access97 database.

 

[TheAceMan1]

How are ya kriehn . . .

You don't need to explicitly open an external Db to access it. Be aware: The [purple]IN clause[/purple] in query/sql is [purple]very powerful![/purple] Try this:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT initials FROM UserDataTable" & _
         "FROM UserDataTable " & _
         "[purple][b]IN[/b][/purple] 'H:\Access\SelfServeTestSchedule.mdb';"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then
      Do
         Me.Combobox2.AddItem rst!initials
         rst.MoveNext
      Loop Until rst.EOF
   Else
      MsgBox "No Records!"
   End If
   
   Set rst = Nothing
   Set db = Nothing
   
End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

Woops! . . . Sorry about the mess! . . .

Should be:

[blue]  Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT initials " & _
         "FROM UserDataTable " & _
         "[purple][b]IN[/b][/purple] 'H:\Access\SelfServeTestSchedule.mdb';"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then
      Do
         Me.Combobox2.AddItem rst!initials
         rst.MoveNext
      Loop Until rst.EOF
   Else
      MsgBox "No Records!"
   End If
   
   Set rst = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[kriehn16]

Thanks AceMan1. That works great. I just want to make sure that I have this right. So instead of explicitly opening an external database, can just set db equal to the current database and use the IN clause with the file path? Do I have to set db equal to the current database?

 

[TheAceMan1]

kriehn . . .

Sorry to get back so late. AceMan was chef at a 4th of July weeked family affair.

[blue] Do I have to set db equal to the current database?[/blue]

Yes! . . . Its the currentdb that [blue]runs the SQL[/blue], [blue]accesses the external Db[/blue], and [blue]sets up the recordset![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886