|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I'm having continuing problems with Access 2003 databases and totally
bogus error messages about exclusive access and what-not. I am the
only user, all the other machines on my network are turned off, and I
have only a single instance of a single database open. Previously,
whenever my VBA code would hit an error, I could not save any changes
until exiting both the database and the VBA editor. Now I've run into
a situation where when I run the code shown below, it runs without
error, but immediately I'm unable to save any changes to any object!
This is slowing my development work to an absolute crawl.
I've seen a number of posts and Knowledgebase articles that talk about
this, and give code for enumerating users, locking them out, etc., etc.
But nothing I can find shows how to simply turn this horrible feature
off. How can I do that once and for all?
Sub ImportDHS()
Dim db As Database, rsDHS As Recordset, rsClient As Recordset, rsChild
As Recordset
Dim FirstName As String, LastName As String
'set objects
Set db = OpenDatabase(Path & DBBack, True)
Set rsDHS = CurrentDb.OpenRecordset("SELECT * FROM [DHS Cases] WHERE
[Mother name] IS NOT NULL")
Set rsClient = db.OpenRecordset("Client")
Set rsChild = db.OpenRecordset("Child")
'loop through DHS Cases records
With rsDHS
Do While Not .EOF
If NameOK(![Mother name], FirstName, LastName) Then
'do stuff here
End If
.MoveNext
Loop
End With
'clear objects
rsChild.Close
Set rsChild = Nothing
rsClient.Close
Set rsClient = Nothing
rsDHS.Close
Set rsDHS = Nothing
db.Close
Set db = Nothing
End Sub
Function NameOK(CombinedName As String, FirstName As String, LastName
As String) As Boolean
Dim CN As String, Lngth As Long, Pos As Long, I As Long
FirstName = ""
LastName = ""
CN = Trim$(Nz(CombinedName, ""))
Lngth = Len(CN)
If Lngth < 3 Then
NameOK = False
Else
Pos = 0
For I = Lngth - 1 To 2 Step -1
If Mid$(CN, I, 1) = " " Then
Pos = I
Exit For
End If
Next I
If Pos = 0 Then
NameOK = False
Else
FirstName = Trim$(Left$(CN, Pos - 1))
LastName = Right$(CN, Lngth - Pos)
NameOK = True
End If
End If
End Function
|
|
I forgot to mention:
1. These problems continue regardless of whether I have the databases
in question set to open Shared or Exclusive in Tools, Options,
Advanced.
2. These problems continue regardless of whether I use Set db =
OpenDatabase(Path & DBBack, True) or Set db = OpenDatabase(Path &
DBBack, False)
3. Path & DBBack are public string constants defining the path and
file name of the external database.
silverbl...[ at ]att.net wrote:
[Quoted Text] > I'm having continuing problems with Access 2003 databases and totally
> bogus error messages about exclusive access and what-not. I am the
> only user, all the other machines on my network are turned off, and I
> have only a single instance of a single database open. Previously,
> whenever my VBA code would hit an error, I could not save any changes
> until exiting both the database and the VBA editor. Now I've run into
> a situation where when I run the code shown below, it runs without
> error, but immediately I'm unable to save any changes to any object!
> This is slowing my development work to an absolute crawl.
>
> I've seen a number of posts and Knowledgebase articles that talk about
> this, and give code for enumerating users, locking them out, etc., etc.
> But nothing I can find shows how to simply turn this horrible feature
> off. How can I do that once and for all?
>
> Sub ImportDHS()
> Dim db As Database, rsDHS As Recordset, rsClient As Recordset, rsChild
> As Recordset
> Dim FirstName As String, LastName As String
> 'set objects
> Set db = OpenDatabase(Path & DBBack, True)
> Set rsDHS = CurrentDb.OpenRecordset("SELECT * FROM [DHS Cases] WHERE
> [Mother name] IS NOT NULL")
> Set rsClient = db.OpenRecordset("Client")
> Set rsChild = db.OpenRecordset("Child")
> 'loop through DHS Cases records
> With rsDHS
> Do While Not .EOF
> If NameOK(![Mother name], FirstName, LastName) Then
> 'do stuff here
> End If
> .MoveNext
> Loop
> End With
> 'clear objects
> rsChild.Close
> Set rsChild = Nothing
> rsClient.Close
> Set rsClient = Nothing
> rsDHS.Close
> Set rsDHS = Nothing
> db.Close
> Set db = Nothing
> End Sub
>
> Function NameOK(CombinedName As String, FirstName As String, LastName
> As String) As Boolean
> Dim CN As String, Lngth As Long, Pos As Long, I As Long
> FirstName = ""
> LastName = ""
> CN = Trim$(Nz(CombinedName, ""))
> Lngth = Len(CN)
> If Lngth < 3 Then
> NameOK = False
> Else
> Pos = 0
> For I = Lngth - 1 To 2 Step -1
> If Mid$(CN, I, 1) = " " Then
> Pos = I
> Exit For
> End If
> Next I
> If Pos = 0 Then
> NameOK = False
> Else
> FirstName = Trim$(Left$(CN, Pos - 1))
> LastName = Right$(CN, Lngth - Pos)
> NameOK = True
> End If
> End If
> End Function
|
|
1) Is "Path & DBBack" pointing to currentDB?
2) dim CDB as dao.database
set cdb = CurrentDB
set rs = cdb.OpenRecordSet
I've only seen that behaviour when I was deliberately trying
to open a second copy of CurrentDB.
(david)
<silverblatt[ at ]att.net> wrote in message
news:1153885151.233300.250630[ at ]h48g2000cwc.googlegroups.com...
[Quoted Text] > I'm having continuing problems with Access 2003 databases and totally
> bogus error messages about exclusive access and what-not. I am the
> only user, all the other machines on my network are turned off, and I
> have only a single instance of a single database open. Previously,
> whenever my VBA code would hit an error, I could not save any changes
> until exiting both the database and the VBA editor. Now I've run into
> a situation where when I run the code shown below, it runs without
> error, but immediately I'm unable to save any changes to any object!
> This is slowing my development work to an absolute crawl.
>
> I've seen a number of posts and Knowledgebase articles that talk about
> this, and give code for enumerating users, locking them out, etc., etc.
> But nothing I can find shows how to simply turn this horrible feature
> off. How can I do that once and for all?
>
> Sub ImportDHS()
> Dim db As Database, rsDHS As Recordset, rsClient As Recordset, rsChild
> As Recordset
> Dim FirstName As String, LastName As String
> 'set objects
> Set db = OpenDatabase(Path & DBBack, True)
> Set rsDHS = CurrentDb.OpenRecordset("SELECT * FROM [DHS Cases] WHERE
> [Mother name] IS NOT NULL")
> Set rsClient = db.OpenRecordset("Client")
> Set rsChild = db.OpenRecordset("Child")
> 'loop through DHS Cases records
> With rsDHS
> Do While Not .EOF
> If NameOK(![Mother name], FirstName, LastName) Then
> 'do stuff here
> End If
> .MoveNext
> Loop
> End With
> 'clear objects
> rsChild.Close
> Set rsChild = Nothing
> rsClient.Close
> Set rsClient = Nothing
> rsDHS.Close
> Set rsDHS = Nothing
> db.Close
> Set db = Nothing
> End Sub
>
> Function NameOK(CombinedName As String, FirstName As String, LastName
> As String) As Boolean
> Dim CN As String, Lngth As Long, Pos As Long, I As Long
> FirstName = ""
> LastName = ""
> CN = Trim$(Nz(CombinedName, ""))
> Lngth = Len(CN)
> If Lngth < 3 Then
> NameOK = False
> Else
> Pos = 0
> For I = Lngth - 1 To 2 Step -1
> If Mid$(CN, I, 1) = " " Then
> Pos = I
> Exit For
> End If
> Next I
> If Pos = 0 Then
> NameOK = False
> Else
> FirstName = Trim$(Left$(CN, Pos - 1))
> LastName = Right$(CN, Lngth - Pos)
> NameOK = True
> End If
> End If
> End Function
>
|
|
No, they are pointing to another database file. However, the recordset
rsDHS is within CurrentDb. Perhaps that is the problem?
david epsom dot com dot au wrote:
[Quoted Text] > 1) Is "Path & DBBack" pointing to currentDB?
>
> 2) dim CDB as dao.database
> set cdb = CurrentDB
> set rs = cdb.OpenRecordSet
>
> I've only seen that behaviour when I was deliberately trying
> to open a second copy of CurrentDB.
>
> (david)
>
>
> <silverblatt[ at ]att.net> wrote in message
> news:1153885151.233300.250630[ at ]h48g2000cwc.googlegroups.com...
> > I'm having continuing problems with Access 2003 databases and totally
> > bogus error messages about exclusive access and what-not. I am the
> > only user, all the other machines on my network are turned off, and I
> > have only a single instance of a single database open. Previously,
> > whenever my VBA code would hit an error, I could not save any changes
> > until exiting both the database and the VBA editor. Now I've run into
> > a situation where when I run the code shown below, it runs without
> > error, but immediately I'm unable to save any changes to any object!
> > This is slowing my development work to an absolute crawl.
> >
> > I've seen a number of posts and Knowledgebase articles that talk about
> > this, and give code for enumerating users, locking them out, etc., etc.
> > But nothing I can find shows how to simply turn this horrible feature
> > off. How can I do that once and for all?
> >
> > Sub ImportDHS()
> > Dim db As Database, rsDHS As Recordset, rsClient As Recordset, rsChild
> > As Recordset
> > Dim FirstName As String, LastName As String
> > 'set objects
> > Set db = OpenDatabase(Path & DBBack, True)
> > Set rsDHS = CurrentDb.OpenRecordset("SELECT * FROM [DHS Cases] WHERE
> > [Mother name] IS NOT NULL")
> > Set rsClient = db.OpenRecordset("Client")
> > Set rsChild = db.OpenRecordset("Child")
> > 'loop through DHS Cases records
> > With rsDHS
> > Do While Not .EOF
> > If NameOK(![Mother name], FirstName, LastName) Then
> > 'do stuff here
> > End If
> > .MoveNext
> > Loop
> > End With
> > 'clear objects
> > rsChild.Close
> > Set rsChild = Nothing
> > rsClient.Close
> > Set rsClient = Nothing
> > rsDHS.Close
> > Set rsDHS = Nothing
> > db.Close
> > Set db = Nothing
> > End Sub
> >
> > Function NameOK(CombinedName As String, FirstName As String, LastName
> > As String) As Boolean
> > Dim CN As String, Lngth As Long, Pos As Long, I As Long
> > FirstName = ""
> > LastName = ""
> > CN = Trim$(Nz(CombinedName, ""))
> > Lngth = Len(CN)
> > If Lngth < 3 Then
> > NameOK = False
> > Else
> > Pos = 0
> > For I = Lngth - 1 To 2 Step -1
> > If Mid$(CN, I, 1) = " " Then
> > Pos = I
> > Exit For
> > End If
> > Next I
> > If Pos = 0 Then
> > NameOK = False
> > Else
> > FirstName = Trim$(Left$(CN, Pos - 1))
> > LastName = Right$(CN, Lngth - Pos)
> > NameOK = True
> > End If
> > End If
> > End Function
> >
|
|
dunno. Try the second suggestion and see if that helps.
(david)
<silverblatt[ at ]att.net> wrote in message
news:1154095041.989328.319020[ at ]s13g2000cwa.googlegroups.com...
[Quoted Text] > No, they are pointing to another database file. However, the recordset
> rsDHS is within CurrentDb. Perhaps that is the problem?
>
> david epsom dot com dot au wrote:
> > 1) Is "Path & DBBack" pointing to currentDB?
> >
> > 2) dim CDB as dao.database
> > set cdb = CurrentDB
> > set rs = cdb.OpenRecordSet
> >
> > I've only seen that behaviour when I was deliberately trying
> > to open a second copy of CurrentDB.
> >
> > (david)
> >
> >
> > <silverblatt[ at ]att.net> wrote in message
> > news:1153885151.233300.250630[ at ]h48g2000cwc.googlegroups.com...
> > > I'm having continuing problems with Access 2003 databases and totally
> > > bogus error messages about exclusive access and what-not. I am the
> > > only user, all the other machines on my network are turned off, and I
> > > have only a single instance of a single database open. Previously,
> > > whenever my VBA code would hit an error, I could not save any changes
> > > until exiting both the database and the VBA editor. Now I've run into
> > > a situation where when I run the code shown below, it runs without
> > > error, but immediately I'm unable to save any changes to any object!
> > > This is slowing my development work to an absolute crawl.
> > >
> > > I've seen a number of posts and Knowledgebase articles that talk about
> > > this, and give code for enumerating users, locking them out, etc.,
etc.
> > > But nothing I can find shows how to simply turn this horrible feature
> > > off. How can I do that once and for all?
> > >
> > > Sub ImportDHS()
> > > Dim db As Database, rsDHS As Recordset, rsClient As Recordset, rsChild
> > > As Recordset
> > > Dim FirstName As String, LastName As String
> > > 'set objects
> > > Set db = OpenDatabase(Path & DBBack, True)
> > > Set rsDHS = CurrentDb.OpenRecordset("SELECT * FROM [DHS Cases] WHERE
> > > [Mother name] IS NOT NULL")
> > > Set rsClient = db.OpenRecordset("Client")
> > > Set rsChild = db.OpenRecordset("Child")
> > > 'loop through DHS Cases records
> > > With rsDHS
> > > Do While Not .EOF
> > > If NameOK(![Mother name], FirstName, LastName) Then
> > > 'do stuff here
> > > End If
> > > .MoveNext
> > > Loop
> > > End With
> > > 'clear objects
> > > rsChild.Close
> > > Set rsChild = Nothing
> > > rsClient.Close
> > > Set rsClient = Nothing
> > > rsDHS.Close
> > > Set rsDHS = Nothing
> > > db.Close
> > > Set db = Nothing
> > > End Sub
> > >
> > > Function NameOK(CombinedName As String, FirstName As String, LastName
> > > As String) As Boolean
> > > Dim CN As String, Lngth As Long, Pos As Long, I As Long
> > > FirstName = ""
> > > LastName = ""
> > > CN = Trim$(Nz(CombinedName, ""))
> > > Lngth = Len(CN)
> > > If Lngth < 3 Then
> > > NameOK = False
> > > Else
> > > Pos = 0
> > > For I = Lngth - 1 To 2 Step -1
> > > If Mid$(CN, I, 1) = " " Then
> > > Pos = I
> > > Exit For
> > > End If
> > > Next I
> > > If Pos = 0 Then
> > > NameOK = False
> > > Else
> > > FirstName = Trim$(Left$(CN, Pos - 1))
> > > LastName = Right$(CN, Lngth - Pos)
> > > NameOK = True
> > > End If
> > > End If
> > > End Function
> > >
>
|
|
In the end, the only thing that got me around this problem was moving
all of my VBA code to a separate database (separate, that is, from the
other two databases whose tables I am working with).
I'm still really appalled that Microsoft has not seen fit to correct
this "feature". This seems to be a pattern with them - they change
something in order to "fix" a problem that no one has, and in the
process they create a real problem for many people.
david[ at ]epsomdotcomdotau wrote:
[Quoted Text] > dunno. Try the second suggestion and see if that helps.
>
> (david)
>
> <silverblatt[ at ]att.net> wrote in message
> news:1154095041.989328.319020[ at ]s13g2000cwa.googlegroups.com...
> > No, they are pointing to another database file. However, the recordset
> > rsDHS is within CurrentDb. Perhaps that is the problem?
> >
> > david epsom dot com dot au wrote:
> > > 1) Is "Path & DBBack" pointing to currentDB?
> > >
> > > 2) dim CDB as dao.database
> > > set cdb = CurrentDB
> > > set rs = cdb.OpenRecordSet
> > >
> > > I've only seen that behaviour when I was deliberately trying
> > > to open a second copy of CurrentDB.
> > >
> > > (david)
> > >
> > >
> > > <silverblatt[ at ]att.net> wrote in message
> > > news:1153885151.233300.250630[ at ]h48g2000cwc.googlegroups.com...
> > > > I'm having continuing problems with Access 2003 databases and totally
> > > > bogus error messages about exclusive access and what-not. I am the
> > > > only user, all the other machines on my network are turned off, and I
> > > > have only a single instance of a single database open. Previously,
> > > > whenever my VBA code would hit an error, I could not save any changes
> > > > until exiting both the database and the VBA editor. Now I've run into
> > > > a situation where when I run the code shown below, it runs without
> > > > error, but immediately I'm unable to save any changes to any object!
> > > > This is slowing my development work to an absolute crawl.
> > > >
> > > > I've seen a number of posts and Knowledgebase articles that talk about
> > > > this, and give code for enumerating users, locking them out, etc.,
> etc.
> > > > But nothing I can find shows how to simply turn this horrible feature
> > > > off. How can I do that once and for all?
> > > >
> > > > Sub ImportDHS()
> > > > Dim db As Database, rsDHS As Recordset, rsClient As Recordset, rsChild
> > > > As Recordset
> > > > Dim FirstName As String, LastName As String
> > > > 'set objects
> > > > Set db = OpenDatabase(Path & DBBack, True)
> > > > Set rsDHS = CurrentDb.OpenRecordset("SELECT * FROM [DHS Cases] WHERE
> > > > [Mother name] IS NOT NULL")
> > > > Set rsClient = db.OpenRecordset("Client")
> > > > Set rsChild = db.OpenRecordset("Child")
> > > > 'loop through DHS Cases records
> > > > With rsDHS
> > > > Do While Not .EOF
> > > > If NameOK(![Mother name], FirstName, LastName) Then
> > > > 'do stuff here
> > > > End If
> > > > .MoveNext
> > > > Loop
> > > > End With
> > > > 'clear objects
> > > > rsChild.Close
> > > > Set rsChild = Nothing
> > > > rsClient.Close
> > > > Set rsClient = Nothing
> > > > rsDHS.Close
> > > > Set rsDHS = Nothing
> > > > db.Close
> > > > Set db = Nothing
> > > > End Sub
> > > >
> > > > Function NameOK(CombinedName As String, FirstName As String, LastName
> > > > As String) As Boolean
> > > > Dim CN As String, Lngth As Long, Pos As Long, I As Long
> > > > FirstName = ""
> > > > LastName = ""
> > > > CN = Trim$(Nz(CombinedName, ""))
> > > > Lngth = Len(CN)
> > > > If Lngth < 3 Then
> > > > NameOK = False
> > > > Else
> > > > Pos = 0
> > > > For I = Lngth - 1 To 2 Step -1
> > > > If Mid$(CN, I, 1) = " " Then
> > > > Pos = I
> > > > Exit For
> > > > End If
> > > > Next I
> > > > If Pos = 0 Then
> > > > NameOK = False
> > > > Else
> > > > FirstName = Trim$(Left$(CN, Pos - 1))
> > > > LastName = Right$(CN, Lngth - Pos)
> > > > NameOK = True
> > > > End If
> > > > End If
> > > > End Function
> > > >
> >
|
|
|
[Quoted Text] > this "feature". This seems to be a pattern with them - they change
> something in order to "fix" a problem that no one has, and in the
> process they create a real problem for many people.
Yes, that seems to be a fair analysis.
(david)
<silverblatt[ at ]att.net> wrote in message
news:1154354277.704826.211470[ at ]m79g2000cwm.googlegroups.com...
> In the end, the only thing that got me around this problem was moving
> all of my VBA code to a separate database (separate, that is, from the
> other two databases whose tables I am working with).
>
> I'm still really appalled that Microsoft has not seen fit to correct
> this "feature". This seems to be a pattern with them - they change
> something in order to "fix" a problem that no one has, and in the
> process they create a real problem for many people.
>
> david[ at ]epsomdotcomdotau wrote:
>> dunno. Try the second suggestion and see if that helps.
>>
>> (david)
>>
>> <silverblatt[ at ]att.net> wrote in message
>> news:1154095041.989328.319020[ at ]s13g2000cwa.googlegroups.com...
>> > No, they are pointing to another database file. However, the recordset
>> > rsDHS is within CurrentDb. Perhaps that is the problem?
>> >
>> > david epsom dot com dot au wrote:
>> > > 1) Is "Path & DBBack" pointing to currentDB?
>> > >
>> > > 2) dim CDB as dao.database
>> > > set cdb = CurrentDB
>> > > set rs = cdb.OpenRecordSet
>> > >
>> > > I've only seen that behaviour when I was deliberately trying
>> > > to open a second copy of CurrentDB.
>> > >
>> > > (david)
>> > >
>> > >
>> > > <silverblatt[ at ]att.net> wrote in message
>> > > news:1153885151.233300.250630[ at ]h48g2000cwc.googlegroups.com...
>> > > > I'm having continuing problems with Access 2003 databases and
>> > > > totally
>> > > > bogus error messages about exclusive access and what-not. I am the
>> > > > only user, all the other machines on my network are turned off, and
>> > > > I
>> > > > have only a single instance of a single database open. Previously,
>> > > > whenever my VBA code would hit an error, I could not save any
>> > > > changes
>> > > > until exiting both the database and the VBA editor. Now I've run
>> > > > into
>> > > > a situation where when I run the code shown below, it runs without
>> > > > error, but immediately I'm unable to save any changes to any
>> > > > object!
>> > > > This is slowing my development work to an absolute crawl.
>> > > >
>> > > > I've seen a number of posts and Knowledgebase articles that talk
>> > > > about
>> > > > this, and give code for enumerating users, locking them out, etc.,
>> etc.
>> > > > But nothing I can find shows how to simply turn this horrible
>> > > > feature
>> > > > off. How can I do that once and for all?
>> > > >
>> > > > Sub ImportDHS()
>> > > > Dim db As Database, rsDHS As Recordset, rsClient As Recordset,
>> > > > rsChild
>> > > > As Recordset
>> > > > Dim FirstName As String, LastName As String
>> > > > 'set objects
>> > > > Set db = OpenDatabase(Path & DBBack, True)
>> > > > Set rsDHS = CurrentDb.OpenRecordset("SELECT * FROM [DHS Cases]
>> > > > WHERE
>> > > > [Mother name] IS NOT NULL")
>> > > > Set rsClient = db.OpenRecordset("Client")
>> > > > Set rsChild = db.OpenRecordset("Child")
>> > > > 'loop through DHS Cases records
>> > > > With rsDHS
>> > > > Do While Not .EOF
>> > > > If NameOK(![Mother name], FirstName, LastName) Then
>> > > > 'do stuff here
>> > > > End If
>> > > > .MoveNext
>> > > > Loop
>> > > > End With
>> > > > 'clear objects
>> > > > rsChild.Close
>> > > > Set rsChild = Nothing
>> > > > rsClient.Close
>> > > > Set rsClient = Nothing
>> > > > rsDHS.Close
>> > > > Set rsDHS = Nothing
>> > > > db.Close
>> > > > Set db = Nothing
>> > > > End Sub
>> > > >
>> > > > Function NameOK(CombinedName As String, FirstName As String,
>> > > > LastName
>> > > > As String) As Boolean
>> > > > Dim CN As String, Lngth As Long, Pos As Long, I As Long
>> > > > FirstName = ""
>> > > > LastName = ""
>> > > > CN = Trim$(Nz(CombinedName, ""))
>> > > > Lngth = Len(CN)
>> > > > If Lngth < 3 Then
>> > > > NameOK = False
>> > > > Else
>> > > > Pos = 0
>> > > > For I = Lngth - 1 To 2 Step -1
>> > > > If Mid$(CN, I, 1) = " " Then
>> > > > Pos = I
>> > > > Exit For
>> > > > End If
>> > > > Next I
>> > > > If Pos = 0 Then
>> > > > NameOK = False
>> > > > Else
>> > > > FirstName = Trim$(Left$(CN, Pos - 1))
>> > > > LastName = Right$(CN, Lngth - Pos)
>> > > > NameOK = True
>> > > > End If
>> > > > End If
>> > > > End Function
>> > > >
>> >
>
|
|
|