how to define the current database

[francky]

I have a problem with this source code :
A message box appears :
Erreur d'exécution '-2147220999 (800401f9)'
"La méthode 'Connection' de l'objet'_CurrentProject' a échoué

------------------------------
Dim cn As ADODB.Connection, Rs1 As ADODB.Recordset, SqlCode As String
Set cn = CurrentProject.Connection
Set Rs1 = New ADODB.Recordset
SqlCode = "Select * From QryDroit where dropt=" & 5
Rs1.Open SqlCode, CurrentProject.Connection

If Rs1.EOF Then
MsgBox "Accès refusé"
Else
DoCmd.OpenForm "FrmDep", , , , , acDialog
End If
-------------------------------

Thanks in advance for your help

Franck

 

[oharab]

Change
SqlCode = "Select * From QryDroit where dropt=" & 5

to

SqlCode = "Select * From QryDroit where dropt=5;"


HTH

ben ----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[francky]

Thanks for your answer Ben but it's not the problem. The program stops at the second line :
Set cn = CurrentProject.Connection !?
and I don't know why.

With Access 97 the syntax was :
Dim db as DataBase
Set db = CurrentDB

 

[cmmrfrds]

What is your OS, Access version, & MDAC (ADO) version? Do you have a reference set to the ADO library?

Dim cn As New ADODB.Connection

You might need the New keyword.

 

[nickjar2]

The way i would do it is like:

dim conn as adodb.connection
Dim rst As ADODB.Recordset
dim sqlCode as string

Set conn = New ADODB.Connection

With conn
.ConnectionString = CurrentProject.Connection
.Open
End With

Set rst = New ADODB.Recordset

SqlCode = "Select * From QryDroit where dropt=" & 5

With rst
.CursorType = adOpenStatic
.ActiveConnection = conn
.Source = sSelect
.Open
End With Nick (Everton Rool OK!)

 

[francky]

I work with W2K, Access 2000
I don't know the MDAC version
I don't understand your last question about the ADO library
It seems there are a lot of differences between access 97 and access 2000

I just want to test if the current user gets the authorization to use an option
I use a table with an option number, a description and an user
The query "QryDroit" selects all the options for the current user
My sql query selects the option 5
If the recordset EOF value is true, the user doesn't get the authorization to use the option

Thanks in advance for your cooperation

Franck

 

[cmmrfrds]

Access 97/2000 can use 2 different libraries for data objects, so you need to separate the objects if you are using both libraries. The libraries are DAO and ADO. ADO is part of what Microsoft calls MDAC.

Windows 2000 has the MDAC libraries built in.

Since there are 2 libraries you need to set explicit references to the data objects in your vba code. While in vba code go up to Tools then References and check that the ADO library is checked. Probably called "Microsoft ActiveX data objects 2.6 library"

The default Recordset Object for Access 2000 mdb is DAO.

As long as you have the ADO reference checked the example provided by nickjar2 should work for you. Otherwise, if you are familiar with DAO go ahead and use it just make sure the data objects are explicitly defined. ie. DAO.Recordset etc...

 

[francky]

Thanks everybody for all
But the problem is always the same even with the Nick's solution
On the line "Set cn = CurrentProject.Connection" or ".ConnectionString = CurrentProject.Connection" from the exemple of Nick, I receive a message box:
Erreur d'exécution '-2147220999 (800401f9)'
"La méthode 'Connection' de l'objet'_CurrentProject' a échoué

Someone has an idea ?

 

[cmmrfrds]

What ADO library to you have referenced? Please type in name.

Please, paste in all your code having anything to do with the connection and recordset objects.

 

[francky]

Thanks for your help. Here is the code

Private Sub CmdOpt5_Click()

Dim cn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim StrSql As String
[Set cn = CurrentProject.Connection] ???
cn.BeginTrans
StrSql = "Select * From QryDroit where dropt= 5"
rst.Open StrSql, cn, adOpenKeyset, adLockOptimistic
If rst.RecordCount = 0 Then
MsgBox "Access Denied"
Else
DoCmd.OpenForm "FrmDep", , , , , acDialog
End If

End Sub

 

[cmmrfrds]

If you continue to have the problem after MAKING SURE the syntax is correct and references are set, then you could check for a problem with the MDAC library.

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q255986&

If necessary, download the Component checker and it will tell you if there is a problem with the library.

 

[nickjar2]

Francky,

U still haven't put the new b4 the connection:

dim cn as adodb.connection

set cn = NEW adodob.connection Nick (Everton Rool OK!)

 

[cmmrfrds]

Try adding New
Dim cn As New ADODB.Connection

What happens if you right click on ADODB? Does Access bring up a definition of all the ADODB objects?

Still need the ADO reference library that you have set.

 

[francky]

I downloaded the MDAC Component Checker and run it.
The result is :
"The MDAC version that is closest to the version on your computer is 2.5 SP1 (2.51.5303.5)
Do I have to upgrade with MDAC 2.5 SP2 or higher ?

Even with "NEW" before adodb.connection, I always receive the same message box !!!

Thanks again for your cooperation

Franck

 

[francky]

OK It's running
I upgraded the MDAC version to 2.6 SP2 and all is alright
Thanks for all and particularly for cmmrfrds ?

Franck