CurrentProject deosnt work in '97 1

[sozzer]

Hi

I have the following code pulling data from a table onto a form. The db was devloped in Access 2003 in 2000 format and runs fine on 2000. However, when in '97, I get an error on the line 'Set cnn = CurrentProject.Connection'

I gather that CurrentProject is not available in '97 but is there a workaround?

Thanks in advance

Dim rs As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strUser as String

Set cnn = CurrentProject.Connection
rs.Open "tblUsers", cnn, 3, 3
strUser = Environ("UserName")
With rs
    .Find "User_Name = '" & strUser & "'"
    Me.Text629.Value = !Admin.Value
    Me.Text85.Value = !FullName.Value
    Me.Text562.Value = !Entitlement.Value
    Me.Text569.Value = !Remaining.Value
    Me.Text575.Value = !TOIL.Value
    Me.Text734.Value = !StaffID
End With
rs.Close
Set cnn = Nothing
Set rs = Nothing

 

[fneily]

ADO was developed for Access 2000 and above. DAO is used in 97. DAO was supposed to disappear in 2000, but after a loud uproar, Microsoft decided to grandfather support for it. So from 2000 on, you can code either DAO and/or ADO but you have to be sure your library references are in the right order.

 

[fneily]

Also, the Provider property was unnecessary for DAO since it was designed for Access, or Jet, databases. In ADO, which is a generic interface, you need the Provider name then make the Connection.

 

[sozzer]

Many thanks. I have set a ref to ADO in '97 - refs are in the following order:

- VBA
- MS Access8.0
- OLE Automation
- MS ADO 2.1
- MS DAO 3.6

So I would have thought that the ADO code should run fine?

Are you saying that I need to further identify which connection I'm using? Or will it just never work, even though I know that ADO does work in '97?

thanks again

 

[PHV]

will it just never work, even though I know that ADO does work in '97?
The Application object of MS Access8.0 doesn't expose a CurrentProject property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

Application.DBEngine(0).Databases(0)

 

[sozzer]

AH HA!

MajP, where does Application.DBEngine(0).Databases(0) go in the code?

many thanks

 

[MajP]

Now that I reread what you are doing it probably will not help . Sorry to mislead you. Can you use DAO? it seems it would be easier working with 97.

 

[PHV]

A starting point:
Dim rs As DAO.Recordset
Set rs = CurrentDB.OpenRecordset("SELECT * FROM tblUsers WHERE User_Name='" & Environ("UserName") & "'")
With rs
If Not (.EOF Or .BOF) Then
Me.Text629.Value = !Admin.Value
Me.Text85.Value = !FullName.Value
Me.Text562.Value = !Entitlement.Value
Me.Text569.Value = !Remaining.Value
Me.Text575.Value = !TOIL.Value
Me.Text734.Value = !StaffID
End If
.Close
End With
Set rs = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sozzer]

i really want to do this in ADO. Is there no way?
cheers

 

[JoeAtWork]

Well, if you want to use ADO you will need to create your own connection object.

    Private DBConn As ADODB.Connection

    Set DBConn = New ADODB.Connection

    With DBConn
        'Not sure which provider would be best for Access97
        .Provider = "Microsoft.Jet.OLEDB.3.51"      
        '.Provider = "Microsoft.Jet.OLEDB.4.0"
        'Give the path to the database here
        .ConnectionString = Application.CurrentDb.Name
        .Open
    End With

Make sure to close the connection when you don't need it anymore (DBConn.Close).

 

[sozzer]

ok, thanks. ill give it a whirl when i get back to work - no '97 at home!