Recordset Connection Problem 2

[toekneel]

We have an Access '97 application for checking out and reserving tools and equipment. We need code that flags an entry when the time period for the reservation is already secured. To accomplish this on my test copy, I have successfully created code that opens up a recordset, compares the dates, and flags dates that overlap.

When attempting to move it into production (on a database with a database password, the main difference between the two), it is failing, indicating that the database is already in use.

The problem appears to be in the connection. Here's the code I'm using:

'Open the connection
Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
.Open "\\server\path\db.mdb", user, password
End With

I've tried to find ways to say "Use this same database and connection that I'm using" but haven't succeeded. Any help?

Tony
Albuquerque, NM

 

[cmmrfrds]

The easiest way to check out your connection on the production PC is to use the udl wizard on the PC to setup and test the connection where you can add the user id and password.

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the microsoft Access provider – this will be Jet 4.0 for Access 2000 or Jet 3.51 for Access 97.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

 

[p27br]

have you tried set cnnConnection = Currentproject.connection ?

 

[toekneel]

I will be testing the udl later this morning. I was thinking that was only for SQL Server connections.

On the cnnConnection=CurrentProject.Connection... I have tried that, but "CurrentProject" is not recognized. What reference library is needed for that to be used?

 

[p27br]

toekneel

the instruction is

Set cnnConnection = CurrentProject.connection instead of
Set cnnConn = New ADODB.Connection

it should work if you have a reference to ADO

 

[cmmrfrds]

CurrentProject is part of the Application, which I don't think was available until Access 2000.

I was looking back at the original post and noticed the example had Microsoft.Jet.OLEDB.4.0. For Access 97 the database is 3.51 not 4.0. There is a separate OLE-DB Provider for 3.51 which you must use for Access 97 databases. Now, if you where connecting to an Access 2000 database from an Access 97 application then it would be 4.0. Hope that helps.

 

[toekneel]

Unfortunately, corporate standard here is Access '97. Long story behind that which I won't get into. CurrentProject isn't available in '97.

Changing the Jet reference fails at that point of code. For whatever reason, the 4.0 is working with the Access 97.

In working with the UDL file- Jet 3.51 is not among the available listings, only 4.0. When testing the connection, I get a message similar to the one I get when in the database: "Test connection failed because of an error in initializing provider. Cannot start your application. The workgroup information file is missing or opened exclusively by another user."

For the record,the error message I have been receiving (and may be misinterpreting) is "Run-Time error. Cannot start your application. The workgroup information file is missing or opened exclusively by another user." have been assuming (since nobody else is in the database) that I was that other user that was being referred to.

Any further thoughts? Thanks for the input to this point!

 

[cmmrfrds]

I think you will need the 3.51 provider, since the 4.0 file structure is not the same. Maybe if you upgrade the MDAC library on the PC the 3.51 will be avialable. MDAC is a free download from Microsoft. It was available on my PC with MDAC version 2.6.

 

[toekneel]

Hmmm... I just tried loading MDAC 2.6 SP2, still getting the same result in the UDL.

 

[cmmrfrds]

I have the 3.51 provider on a couple of my PC's, but they both have Access 2000 on them. Then, I don't know when it gets loaded.

 

[cmmrfrds]

Maybe these links will help.

http://groups.yahoo.com/group/vbhelp/message/8474

http://support.microsoft.com/defaul...port/kb/articles/Q197/9/02.ASP&NoWebContent=1

They indicate that in order to get the 3.51 provider that MDAC 2.0 must be installed first and then overlayed with the newer version. I don't know if this would cause any problems, but you may need to do some more research.

 

[p27br]

I would suggest using DAO objects if you're developing under Access 97.

 

[toekneel]

From this article, it appears that earlier versions of MDAC will not work with WinXP.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/data_mdacinstall.asp

On the DAO Objects... what would the commands be to properly reference the current database connection in opening a recordset?

 

[toekneel]

I've found a copy of MDAC 2.0 from this link:

http://www.bytebloc.com/MDAC20.html

I'll keep working on this and see where it gets me.

 

[cmmrfrds]

The site you indicated gives a good overview on the different versions of mdac and the various windows/access versions. There is lots of code on this site about using DAO. Probably search for "currentdb" and you will find some examples.

 

[p27br]

With DAO :

Dim db as DAO.Database

set db = CurrentDb()

that will set a reference to your current DB

 

[toekneel]

Once again, it works on my copy on my C:\ drive, while failing on the production copy. Does the database password make any difference with coding, beyond ".open db,"username", "password""?

When I try to compile this, it indicates a type mismatch.

Thanks for all the help I'm getting on this! Every time I come with a problem on tek-tips I get some great education!

 

[cmmrfrds]

The DAO and ADO libraries have some methods/objects with the same name. For instance, Recordset, Field, and others. So, if both libraries are referenced in an application it is best to explicitly identify these objects by prefixing with the library name.

DAO.Recordset or ADODB.Recordset

Or
if not using the ADO library then, delete the reference.

 

[toekneel]

This will do it. I removed the reference to ADO, the used the following lines of code:

Dim rstReservedDate As Recordset
Dim dbsFleet As Database
Dim strSQL As String

strSQL = "SELECT * FROM tblReservations"

Set dbsFleet = CurrentDb()
Set rstReservedDate = dbsFleet.OpenRecordset(strSQL, dbOpenSnapshot)

Thanks for the help!!

Tony