Run-time error -2147467259 , I'm going insane. 1

[rssql]

Run-time error: -2147467259 (80004005)
The database has been placed in a state by user 'xxx' on machine 'yyy' that prevents it from being opened or locked.

i'm so tired of this.

using Office 2k sr1, straight off the cd. I even tried to install SR-1/1a, but it says it already exists.

I found MS technote Q248967 that talks about this, but I'm not using subforms on the problem forms.

I also happen to have installed Visual Studio 6 if it matters.

The following code is typical in my application. I do this several times in the app.

It intermittantly gives me the error on the line with the
">>>".

*******************************************
Dim sql1 as string
Dim MyCnn as New ADODB.Connection
Dim MyRst as New ADODB.RecordSet

With
.Provider = "Microsoft.Jet.OLEDB.4.0"
>>> .Open Forms!MainMenu!MyDataPath & "\MyFile.mdb"
End With

sql1 = "SELECT * FROM SOMETABLE"

With MyRst
Set .ActiveConnection = MyCnn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open sql1
End With

'some code here

Set MyRst = Nothing
MyCnn.Close
*****************************************

 

[SBendBuckeye]

An alternate way of doing that would be

.Open Forms("MainMenu&quot.MyDataPath & "\MyFile.mdb"

I don't know if it would change anything, but might be worth a try. Also, is MyDataPath trimmed and validated to ensure there are no leading, trailing or embedded spaces?

Good Luck! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[cmmrfrds]

What happens when you hard code the path? Is there more to the connection string than what you are showing?

Also check out by printing the concatenated string before including in the connection.

debug.print Forms!MainMenu!MyDataPath & "\MyFile.mdb"

With
.Provider = "Microsoft.Jet.OLEDB.4.0"
>>> .Open Forms!MainMenu!MyDataPath & "\MyFile.mdb"
End With

Here is an example of a connection that works, the syntax you have does not look correct.

connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\guppy\CWellsFargo\LSTMonthendReport.mdb;" & _
"Persist Security Info=False"

cn.ConnectionString = connString
cn.Open connString

 

[rssql]

thanks for the reply.

the path is correct. i checked it in debug, it has no leading or trailing characters.

on my above example i forgot the: "MyCnn" as in the statement:
With MyCnn

but it's really in my code...

I use this basic format many times in the app.

Should i only have one time in the whole app where i create the MyCnn? and then only close it once in the app?

I found a webpage that talks about getting this error:

http://www.p2p.wrox.com/archive/access/2001-06/19.asp

also found one at MS.
technote 307640 - When You Try to Open the Same MDB File from Multiple Instances

 

[cmmrfrds]

Are you trying to establish a connection to the MDB file that you have open? That will cause the error. Better to use the
currentproject.connection
instead.

It is a good idea to only have 1 connection open to the access mdb as there are a limited number of concurrent connections supported in Access.

 

[rssql]

Yes, i am trying to establish a conn to the MDB that my code is running in.

I replaced my .Open statements with .Open CurrentProject.Connection, and get the same problem.
here is the Connection string text from the immediate window:

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\CBR\cbr2k.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=E:\PROGRA~1\COMMON~1\System\SYSTEM.MDW;Jet OLEDB:Registry Path="";Jet OLEDBatabase Password="";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

 

[cmmrfrds]

Did you use the current connection in the recordset or did you try to open the current connection again?

This should work.
With MyRst
Set .ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open sql1
End With

 

[rssql]

do you mean i then don't have to create the connection at all?
ie: dim cnn as New ADODB.Connection ?
Here's my REAL code.


Dim strSQL1 As String
Dim cnnCBR5 As New ADODB.Connection
Dim rstOUT As New ADODB.Recordset

With cnnCBR5
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open CurrentProject.Connection 'Forms!mainmenu!CBRDataPath & "\cbr2k.mdb"
End With

strSQL1 = "SELECT * FROM FinancialCoverSheetOutstandingExpTable"

With rstOUT
Set .ActiveConnection = cnnCBR5
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open strSQL1
End With

'some code....

Set rstOUT = Nothing
cnnCBR5.Close

 

[cmmrfrds]

Right, currentproject.connection is already a connection to the current mdb file you are in. All you need to do is reference the connection in the Recordset Open.

DO NOT DO THIS.
With cnnCBR5
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open CurrentProject.Connection 'Forms!mainmenu!CBRDataPath & "\cbr2k.mdb"
End With

BUT COULD DO THIS.
Set cnnCBR5 = CurrentProject.Connection
With rstOUT
.ActiveConnection = cnnCBR5
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open strSQL1
End With

OR THIS
With rstOUT
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open strSQL1
End With

 

[rssql]

thanks,

also, i connect to another mdb from this file. any good/efficient ideas along this line?

 

[cmmrfrds]

Just set up a connection string and you should be on your way. If you are using it many times in the app there may be an advantage to set it up as a public object in the standard module and keep reusing it throughout the app. You can close the connection and reopen at any point, but when finished set it to nothing which removes the object from memory.