I am trying to compact another database from VBA, but when I run the
following code, I get this error:

3734 The database has been placed in a state by user 'me' on machine
'mymachine' that prevents it from being opened or locked.

Code:
Public Sub compact_delete()

Dim subDBNAME As String
Dim tbl As Variant
Dim tbllist As Variant
Dim tblbk As String
Dim tblname As Variant
Dim tblname2 As Variant
Dim masterdb As Database
Dim compactname As String
Dim backupname As String
Dim dbname As String
'Step .5 - Build constants

On Error GoTo errhand
'File name
subDBNAME = "F:\projects\Issue Track Projects\IT-001-000X- archive and
others\Comerica Inc Issue Tracking Database"
dbname = subDBNAME & ".mdb"
compactname = subDBNAME & ".cpk"
backupname = subDBNAME & ".old"

'tables to be archived
tbllist = Array("[Master Recommendation Table].[Issue Reference
Number]", "tblMitigatingControl.[Ref Tracking]",
"tblInternalComments.[Tracking Number]", "tblPostedComments.[Ref Tracking]",
"tblClientResponse.[Ref Tracking]", "tm****UETYPETABLE.txtREF_TRACK_NO")

Set masterdb = OpenDatabase(dbname)

'For Each tbl In tbllist
' If InStr(tbl, " ") < InStr(tbl, ".") And InStr(tbl, " ") <> 0 Then
' tbl = Mid(tbl, 2, InStr(tbl, ".") - 3)
' Else
' tbl = Mid(tbl, 1, InStr(tbl, ".") - 1)
' End If
' tblbk = tbl & "_bk"
' masterdb.TableDefs.Delete tblbk
'Next

masterdb.Close
Set masterdb = Nothing

FileCopy dbname, backupname

DBEngine.CompactDatabase dbname, compactname

FileCopy compactname, dbname

errhand:
Debug.Print Err.Number; Err.Description
End Sub

I eventually want to put the delete tables back in, but won't until I get
this fixed. Thanks
--
Yours Fictionally, Biggles

Hi,
perhaps it still opened by jet, try like this:

FileCopy dbname, backupname

DBEngine.CompactDatabase backupname, compactname

FileCopy compactname, dbname


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


"Biggles" <(E-Mail Removed)> wrote in message
news:595B10A2-C69C-4DFE-A3D8-(E-Mail Removed)...
>I am trying to compact another database from VBA, but when I run the
> following code, I get this error:
>
> 3734 The database has been placed in a state by user 'me' on machine
> 'mymachine' that prevents it from being opened or locked.
>
> Code:
> Public Sub compact_delete()
>
> Dim subDBNAME As String
> Dim tbl As Variant
> Dim tbllist As Variant
> Dim tblbk As String
> Dim tblname As Variant
> Dim tblname2 As Variant
> Dim masterdb As Database
> Dim compactname As String
> Dim backupname As String
> Dim dbname As String
> 'Step .5 - Build constants
>
> On Error GoTo errhand
> 'File name
> subDBNAME = "F:\projects\Issue Track Projects\IT-001-000X- archive and
> others\Comerica Inc Issue Tracking Database"
> dbname = subDBNAME & ".mdb"
> compactname = subDBNAME & ".cpk"
> backupname = subDBNAME & ".old"
>
> 'tables to be archived
> tbllist = Array("[Master Recommendation Table].[Issue Reference
> Number]", "tblMitigatingControl.[Ref Tracking]",
> "tblInternalComments.[Tracking Number]", "tblPostedComments.[Ref
> Tracking]",
> "tblClientResponse.[Ref Tracking]", "tm****UETYPETABLE.txtREF_TRACK_NO")
>
> Set masterdb = OpenDatabase(dbname)
>
> 'For Each tbl In tbllist
> ' If InStr(tbl, " ") < InStr(tbl, ".") And InStr(tbl, " ") <> 0 Then
> ' tbl = Mid(tbl, 2, InStr(tbl, ".") - 3)
> ' Else
> ' tbl = Mid(tbl, 1, InStr(tbl, ".") - 1)
> ' End If
> ' tblbk = tbl & "_bk"
> ' masterdb.TableDefs.Delete tblbk
> 'Next
>
> masterdb.Close
> Set masterdb = Nothing
>
> FileCopy dbname, backupname
>
> DBEngine.CompactDatabase dbname, compactname
>
> FileCopy compactname, dbname
>
> errhand:
> Debug.Print Err.Number; Err.Description
> End Sub
>
> I eventually want to put the delete tables back in, but won't until I get
> this fixed. Thanks
> --
> Yours Fictionally, Biggles

Alex

Thanks, but I am still getting the same error, and I know it is on the
Compact statement. Is it possible I am not closing the opendatabase properly?
--
Ficticiously Yours, Biggles


"Alex Dybenko" wrote:

> Hi,
> perhaps it still opened by jet, try like this:
>
> FileCopy dbname, backupname
>
> DBEngine.CompactDatabase backupname, compactname
>
> FileCopy compactname, dbname
>
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://alexdyb.blogspot.com
> http://www.PointLtd.com
>
>
> "Biggles" <(E-Mail Removed)> wrote in message
> news:595B10A2-C69C-4DFE-A3D8-(E-Mail Removed)...
> >I am trying to compact another database from VBA, but when I run the
> > following code, I get this error:
> >
> > 3734 The database has been placed in a state by user 'me' on machine
> > 'mymachine' that prevents it from being opened or locked.
> >
> > Code:
> > Public Sub compact_delete()
> >
> > Dim subDBNAME As String
> > Dim tbl As Variant
> > Dim tbllist As Variant
> > Dim tblbk As String
> > Dim tblname As Variant
> > Dim tblname2 As Variant
> > Dim masterdb As Database
> > Dim compactname As String
> > Dim backupname As String
> > Dim dbname As String
> > 'Step .5 - Build constants
> >
> > On Error GoTo errhand
> > 'File name
> > subDBNAME = "F:\projects\Issue Track Projects\IT-001-000X- archive and
> > others\Comerica Inc Issue Tracking Database"
> > dbname = subDBNAME & ".mdb"
> > compactname = subDBNAME & ".cpk"
> > backupname = subDBNAME & ".old"
> >
> > 'tables to be archived
> > tbllist = Array("[Master Recommendation Table].[Issue Reference
> > Number]", "tblMitigatingControl.[Ref Tracking]",
> > "tblInternalComments.[Tracking Number]", "tblPostedComments.[Ref
> > Tracking]",
> > "tblClientResponse.[Ref Tracking]", "tm****UETYPETABLE.txtREF_TRACK_NO")
> >
> > Set masterdb = OpenDatabase(dbname)
> >
> > 'For Each tbl In tbllist
> > ' If InStr(tbl, " ") < InStr(tbl, ".") And InStr(tbl, " ") <> 0 Then
> > ' tbl = Mid(tbl, 2, InStr(tbl, ".") - 3)
> > ' Else
> > ' tbl = Mid(tbl, 1, InStr(tbl, ".") - 1)
> > ' End If
> > ' tblbk = tbl & "_bk"
> > ' masterdb.TableDefs.Delete tblbk
> > 'Next
> >
> > masterdb.Close
> > Set masterdb = Nothing
> >
> > FileCopy dbname, backupname
> >
> > DBEngine.CompactDatabase dbname, compactname
> >
> > FileCopy compactname, dbname
> >
> > errhand:
> > Debug.Print Err.Number; Err.Description
> > End Sub
> >
> > I eventually want to put the delete tables back in, but won't until I get
> > this fixed. Thanks
> > --
> > Yours Fictionally, Biggles
>
>

What are you doing to try and close the open database?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Biggles" <(E-Mail Removed)> wrote in message
news:86F2068F-F69B-4B9A-8C1F-(E-Mail Removed)...
> Alex
>
> Thanks, but I am still getting the same error, and I know it is on the
> Compact statement. Is it possible I am not closing the opendatabase
> properly?
> --
> Ficticiously Yours, Biggles
>
>
> "Alex Dybenko" wrote:
>
>> Hi,
>> perhaps it still opened by jet, try like this:
>>
>> FileCopy dbname, backupname
>>
>> DBEngine.CompactDatabase backupname, compactname
>>
>> FileCopy compactname, dbname
>>
>>
>> --
>> Best regards,
>> ___________
>> Alex Dybenko (MVP)
>> http://alexdyb.blogspot.com
>> http://www.PointLtd.com
>>
>>
>> "Biggles" <(E-Mail Removed)> wrote in message
>> news:595B10A2-C69C-4DFE-A3D8-(E-Mail Removed)...
>> >I am trying to compact another database from VBA, but when I run the
>> > following code, I get this error:
>> >
>> > 3734 The database has been placed in a state by user 'me' on machine
>> > 'mymachine' that prevents it from being opened or locked.
>> >
>> > Code:
>> > Public Sub compact_delete()
>> >
>> > Dim subDBNAME As String
>> > Dim tbl As Variant
>> > Dim tbllist As Variant
>> > Dim tblbk As String
>> > Dim tblname As Variant
>> > Dim tblname2 As Variant
>> > Dim masterdb As Database
>> > Dim compactname As String
>> > Dim backupname As String
>> > Dim dbname As String
>> > 'Step .5 - Build constants
>> >
>> > On Error GoTo errhand
>> > 'File name
>> > subDBNAME = "F:\projects\Issue Track Projects\IT-001-000X- archive
>> > and
>> > others\Comerica Inc Issue Tracking Database"
>> > dbname = subDBNAME & ".mdb"
>> > compactname = subDBNAME & ".cpk"
>> > backupname = subDBNAME & ".old"
>> >
>> > 'tables to be archived
>> > tbllist = Array("[Master Recommendation Table].[Issue Reference
>> > Number]", "tblMitigatingControl.[Ref Tracking]",
>> > "tblInternalComments.[Tracking Number]", "tblPostedComments.[Ref
>> > Tracking]",
>> > "tblClientResponse.[Ref Tracking]",
>> > "tm****UETYPETABLE.txtREF_TRACK_NO")
>> >
>> > Set masterdb = OpenDatabase(dbname)
>> >
>> > 'For Each tbl In tbllist
>> > ' If InStr(tbl, " ") < InStr(tbl, ".") And InStr(tbl, " ") <> 0
>> > Then
>> > ' tbl = Mid(tbl, 2, InStr(tbl, ".") - 3)
>> > ' Else
>> > ' tbl = Mid(tbl, 1, InStr(tbl, ".") - 1)
>> > ' End If
>> > ' tblbk = tbl & "_bk"
>> > ' masterdb.TableDefs.Delete tblbk
>> > 'Next
>> >
>> > masterdb.Close
>> > Set masterdb = Nothing
>> >
>> > FileCopy dbname, backupname
>> >
>> > DBEngine.CompactDatabase dbname, compactname
>> >
>> > FileCopy compactname, dbname
>> >
>> > errhand:
>> > Debug.Print Err.Number; Err.Description
>> > End Sub
>> >
>> > I eventually want to put the delete tables back in, but won't until I
>> > get
>> > this fixed. Thanks
>> > --
>> > Yours Fictionally, Biggles
>>
>>

See full code below:

Set masterdb = opendatabase(dbname)

do stuff (which is commented out right now)

masterdb.close
Set masterdb = nothing
--
Ficticiously Yours, Biggles


"Douglas J. Steele" wrote:

> What are you doing to try and close the open database?
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Biggles" <(E-Mail Removed)> wrote in message
> news:86F2068F-F69B-4B9A-8C1F-(E-Mail Removed)...
> > Alex
> >
> > Thanks, but I am still getting the same error, and I know it is on the
> > Compact statement. Is it possible I am not closing the opendatabase
> > properly?
> > --
> > Ficticiously Yours, Biggles
> >
> >
> > "Alex Dybenko" wrote:
> >
> >> Hi,
> >> perhaps it still opened by jet, try like this:
> >>
> >> FileCopy dbname, backupname
> >>
> >> DBEngine.CompactDatabase backupname, compactname
> >>
> >> FileCopy compactname, dbname
> >>
> >>
> >> --
> >> Best regards,
> >> ___________
> >> Alex Dybenko (MVP)
> >> http://alexdyb.blogspot.com
> >> http://www.PointLtd.com
> >>
> >>
> >> "Biggles" <(E-Mail Removed)> wrote in message
> >> news:595B10A2-C69C-4DFE-A3D8-(E-Mail Removed)...
> >> >I am trying to compact another database from VBA, but when I run the
> >> > following code, I get this error:
> >> >
> >> > 3734 The database has been placed in a state by user 'me' on machine
> >> > 'mymachine' that prevents it from being opened or locked.
> >> >
> >> > Code:
> >> > Public Sub compact_delete()
> >> >
> >> > Dim subDBNAME As String
> >> > Dim tbl As Variant
> >> > Dim tbllist As Variant
> >> > Dim tblbk As String
> >> > Dim tblname As Variant
> >> > Dim tblname2 As Variant
> >> > Dim masterdb As Database
> >> > Dim compactname As String
> >> > Dim backupname As String
> >> > Dim dbname As String
> >> > 'Step .5 - Build constants
> >> >
> >> > On Error GoTo errhand
> >> > 'File name
> >> > subDBNAME = "F:\projects\Issue Track Projects\IT-001-000X- archive
> >> > and
> >> > others\Comerica Inc Issue Tracking Database"
> >> > dbname = subDBNAME & ".mdb"
> >> > compactname = subDBNAME & ".cpk"
> >> > backupname = subDBNAME & ".old"
> >> >
> >> > 'tables to be archived
> >> > tbllist = Array("[Master Recommendation Table].[Issue Reference
> >> > Number]", "tblMitigatingControl.[Ref Tracking]",
> >> > "tblInternalComments.[Tracking Number]", "tblPostedComments.[Ref
> >> > Tracking]",
> >> > "tblClientResponse.[Ref Tracking]",
> >> > "tm****UETYPETABLE.txtREF_TRACK_NO")
> >> >
> >> > Set masterdb = OpenDatabase(dbname)
> >> >
> >> > 'For Each tbl In tbllist
> >> > ' If InStr(tbl, " ") < InStr(tbl, ".") And InStr(tbl, " ") <> 0
> >> > Then
> >> > ' tbl = Mid(tbl, 2, InStr(tbl, ".") - 3)
> >> > ' Else
> >> > ' tbl = Mid(tbl, 1, InStr(tbl, ".") - 1)
> >> > ' End If
> >> > ' tblbk = tbl & "_bk"
> >> > ' masterdb.TableDefs.Delete tblbk
> >> > 'Next
> >> >
> >> > masterdb.Close
> >> > Set masterdb = Nothing
> >> >
> >> > FileCopy dbname, backupname
> >> >
> >> > DBEngine.CompactDatabase dbname, compactname
> >> >
> >> > FileCopy compactname, dbname
> >> >
> >> > errhand:
> >> > Debug.Print Err.Number; Err.Description
> >> > End Sub
> >> >
> >> > I eventually want to put the delete tables back in, but won't until I
> >> > get
> >> > this fixed. Thanks
> >> > --
> >> > Yours Fictionally, Biggles
> >>
> >>
>
>
>

Are you certain no other connections exist to the backend (say, a bound form
is open)?

Check whether the .LDB file exists (in the same folder as the .MDB you're
trying to compact). If it does, you haven't closed it properly. If it
doesn't, you should be fine.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Biggles" <(E-Mail Removed)> wrote in message
news:37A62317-9012-4B72-A9E9-(E-Mail Removed)...
> See full code below:
>
> Set masterdb = opendatabase(dbname)
>
> do stuff (which is commented out right now)
>
> masterdb.close
> Set masterdb = nothing
> --
> Ficticiously Yours, Biggles
>
>
> "Douglas J. Steele" wrote:
>
>> What are you doing to try and close the open database?
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Biggles" <(E-Mail Removed)> wrote in message
>> news:86F2068F-F69B-4B9A-8C1F-(E-Mail Removed)...
>> > Alex
>> >
>> > Thanks, but I am still getting the same error, and I know it is on the
>> > Compact statement. Is it possible I am not closing the opendatabase
>> > properly?
>> > --
>> > Ficticiously Yours, Biggles
>> >
>> >
>> > "Alex Dybenko" wrote:
>> >
>> >> Hi,
>> >> perhaps it still opened by jet, try like this:
>> >>
>> >> FileCopy dbname, backupname
>> >>
>> >> DBEngine.CompactDatabase backupname, compactname
>> >>
>> >> FileCopy compactname, dbname
>> >>
>> >>
>> >> --
>> >> Best regards,
>> >> ___________
>> >> Alex Dybenko (MVP)
>> >> http://alexdyb.blogspot.com
>> >> http://www.PointLtd.com
>> >>
>> >>
>> >> "Biggles" <(E-Mail Removed)> wrote in message
>> >> news:595B10A2-C69C-4DFE-A3D8-(E-Mail Removed)...
>> >> >I am trying to compact another database from VBA, but when I run the
>> >> > following code, I get this error:
>> >> >
>> >> > 3734 The database has been placed in a state by user 'me' on machine
>> >> > 'mymachine' that prevents it from being opened or locked.
>> >> >
>> >> > Code:
>> >> > Public Sub compact_delete()
>> >> >
>> >> > Dim subDBNAME As String
>> >> > Dim tbl As Variant
>> >> > Dim tbllist As Variant
>> >> > Dim tblbk As String
>> >> > Dim tblname As Variant
>> >> > Dim tblname2 As Variant
>> >> > Dim masterdb As Database
>> >> > Dim compactname As String
>> >> > Dim backupname As String
>> >> > Dim dbname As String
>> >> > 'Step .5 - Build constants
>> >> >
>> >> > On Error GoTo errhand
>> >> > 'File name
>> >> > subDBNAME = "F:\projects\Issue Track Projects\IT-001-000X-
>> >> > archive
>> >> > and
>> >> > others\Comerica Inc Issue Tracking Database"
>> >> > dbname = subDBNAME & ".mdb"
>> >> > compactname = subDBNAME & ".cpk"
>> >> > backupname = subDBNAME & ".old"
>> >> >
>> >> > 'tables to be archived
>> >> > tbllist = Array("[Master Recommendation Table].[Issue Reference
>> >> > Number]", "tblMitigatingControl.[Ref Tracking]",
>> >> > "tblInternalComments.[Tracking Number]", "tblPostedComments.[Ref
>> >> > Tracking]",
>> >> > "tblClientResponse.[Ref Tracking]",
>> >> > "tm****UETYPETABLE.txtREF_TRACK_NO")
>> >> >
>> >> > Set masterdb = OpenDatabase(dbname)
>> >> >
>> >> > 'For Each tbl In tbllist
>> >> > ' If InStr(tbl, " ") < InStr(tbl, ".") And InStr(tbl, " ") <>
>> >> > 0
>> >> > Then
>> >> > ' tbl = Mid(tbl, 2, InStr(tbl, ".") - 3)
>> >> > ' Else
>> >> > ' tbl = Mid(tbl, 1, InStr(tbl, ".") - 1)
>> >> > ' End If
>> >> > ' tblbk = tbl & "_bk"
>> >> > ' masterdb.TableDefs.Delete tblbk
>> >> > 'Next
>> >> >
>> >> > masterdb.Close
>> >> > Set masterdb = Nothing
>> >> >
>> >> > FileCopy dbname, backupname
>> >> >
>> >> > DBEngine.CompactDatabase dbname, compactname
>> >> >
>> >> > FileCopy compactname, dbname
>> >> >
>> >> > errhand:
>> >> > Debug.Print Err.Number; Err.Description
>> >> > End Sub
>> >> >
>> >> > I eventually want to put the delete tables back in, but won't until
>> >> > I
>> >> > get
>> >> > this fixed. Thanks
>> >> > --
>> >> > Yours Fictionally, Biggles
>> >>
>> >>
>>
>>
>>

Ok, I checked that by stepping through and watching the windows explorer
window. I see the ldb file created on the Set...Opendatabase and then go
away on the masterdb.close and set ...nothing. I am still getting the same
error.

Any other ideas? This should be the easiest part of the process I am trying
to perform, that's why it bugs me so.
--
Ficticiously Yours, Biggles


"Douglas J. Steele" wrote:

> Are you certain no other connections exist to the backend (say, a bound form
> is open)?
>
> Check whether the .LDB file exists (in the same folder as the .MDB you're
> trying to compact). If it does, you haven't closed it properly. If it
> doesn't, you should be fine.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Biggles" <(E-Mail Removed)> wrote in message
> news:37A62317-9012-4B72-A9E9-(E-Mail Removed)...
> > See full code below:
> >
> > Set masterdb = opendatabase(dbname)
> >
> > do stuff (which is commented out right now)
> >
> > masterdb.close
> > Set masterdb = nothing
> > --
> > Ficticiously Yours, Biggles
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> What are you doing to try and close the open database?
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "Biggles" <(E-Mail Removed)> wrote in message
> >> news:86F2068F-F69B-4B9A-8C1F-(E-Mail Removed)...
> >> > Alex
> >> >
> >> > Thanks, but I am still getting the same error, and I know it is on the
> >> > Compact statement. Is it possible I am not closing the opendatabase
> >> > properly?
> >> > --
> >> > Ficticiously Yours, Biggles
> >> >
> >> >
> >> > "Alex Dybenko" wrote:
> >> >
> >> >> Hi,
> >> >> perhaps it still opened by jet, try like this:
> >> >>
> >> >> FileCopy dbname, backupname
> >> >>
> >> >> DBEngine.CompactDatabase backupname, compactname
> >> >>
> >> >> FileCopy compactname, dbname
> >> >>
> >> >>
> >> >> --
> >> >> Best regards,
> >> >> ___________
> >> >> Alex Dybenko (MVP)
> >> >> http://alexdyb.blogspot.com
> >> >> http://www.PointLtd.com
> >> >>
> >> >>
> >> >> "Biggles" <(E-Mail Removed)> wrote in message
> >> >> news:595B10A2-C69C-4DFE-A3D8-(E-Mail Removed)...
> >> >> >I am trying to compact another database from VBA, but when I run the
> >> >> > following code, I get this error:
> >> >> >
> >> >> > 3734 The database has been placed in a state by user 'me' on machine
> >> >> > 'mymachine' that prevents it from being opened or locked.
> >> >> >
> >> >> > Code:
> >> >> > Public Sub compact_delete()
> >> >> >
> >> >> > Dim subDBNAME As String
> >> >> > Dim tbl As Variant
> >> >> > Dim tbllist As Variant
> >> >> > Dim tblbk As String
> >> >> > Dim tblname As Variant
> >> >> > Dim tblname2 As Variant
> >> >> > Dim masterdb As Database
> >> >> > Dim compactname As String
> >> >> > Dim backupname As String
> >> >> > Dim dbname As String
> >> >> > 'Step .5 - Build constants
> >> >> >
> >> >> > On Error GoTo errhand
> >> >> > 'File name
> >> >> > subDBNAME = "F:\projects\Issue Track Projects\IT-001-000X-
> >> >> > archive
> >> >> > and
> >> >> > others\Comerica Inc Issue Tracking Database"
> >> >> > dbname = subDBNAME & ".mdb"
> >> >> > compactname = subDBNAME & ".cpk"
> >> >> > backupname = subDBNAME & ".old"
> >> >> >
> >> >> > 'tables to be archived
> >> >> > tbllist = Array("[Master Recommendation Table].[Issue Reference
> >> >> > Number]", "tblMitigatingControl.[Ref Tracking]",
> >> >> > "tblInternalComments.[Tracking Number]", "tblPostedComments.[Ref
> >> >> > Tracking]",
> >> >> > "tblClientResponse.[Ref Tracking]",
> >> >> > "tm****UETYPETABLE.txtREF_TRACK_NO")
> >> >> >
> >> >> > Set masterdb = OpenDatabase(dbname)
> >> >> >
> >> >> > 'For Each tbl In tbllist
> >> >> > ' If InStr(tbl, " ") < InStr(tbl, ".") And InStr(tbl, " ") <>
> >> >> > 0
> >> >> > Then
> >> >> > ' tbl = Mid(tbl, 2, InStr(tbl, ".") - 3)
> >> >> > ' Else
> >> >> > ' tbl = Mid(tbl, 1, InStr(tbl, ".") - 1)
> >> >> > ' End If
> >> >> > ' tblbk = tbl & "_bk"
> >> >> > ' masterdb.TableDefs.Delete tblbk
> >> >> > 'Next
> >> >> >
> >> >> > masterdb.Close
> >> >> > Set masterdb = Nothing
> >> >> >
> >> >> > FileCopy dbname, backupname
> >> >> >
> >> >> > DBEngine.CompactDatabase dbname, compactname
> >> >> >
> >> >> > FileCopy compactname, dbname
> >> >> >
> >> >> > errhand:
> >> >> > Debug.Print Err.Number; Err.Description
> >> >> > End Sub
> >> >> >
> >> >> > I eventually want to put the delete tables back in, but won't until
> >> >> > I
> >> >> > get
> >> >> > this fixed. Thanks
> >> >> > --
> >> >> > Yours Fictionally, Biggles
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

You sure about that error number? The AccessError function doesn't seem to
have a description for 3734.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Biggles" <(E-Mail Removed)> wrote in message
news:28AFDEFA-426D-4167-8B43-(E-Mail Removed)...
> Ok, I checked that by stepping through and watching the windows explorer
> window. I see the ldb file created on the Set...Opendatabase and then go
> away on the masterdb.close and set ...nothing. I am still getting the
> same
> error.
>
> Any other ideas? This should be the easiest part of the process I am
> trying
> to perform, that's why it bugs me so.
> --
> Ficticiously Yours, Biggles
>
>
> "Douglas J. Steele" wrote:
>
>> Are you certain no other connections exist to the backend (say, a bound
>> form
>> is open)?
>>
>> Check whether the .LDB file exists (in the same folder as the .MDB you're
>> trying to compact). If it does, you haven't closed it properly. If it
>> doesn't, you should be fine.
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Biggles" <(E-Mail Removed)> wrote in message
>> news:37A62317-9012-4B72-A9E9-(E-Mail Removed)...
>> > See full code below:
>> >
>> > Set masterdb = opendatabase(dbname)
>> >
>> > do stuff (which is commented out right now)
>> >
>> > masterdb.close
>> > Set masterdb = nothing
>> > --
>> > Ficticiously Yours, Biggles
>> >
>> >
>> > "Douglas J. Steele" wrote:
>> >
>> >> What are you doing to try and close the open database?
>> >>
>> >> --
>> >> Doug Steele, Microsoft Access MVP
>> >> http://I.Am/DougSteele
>> >> (no e-mails, please!)
>> >>
>> >>
>> >> "Biggles" <(E-Mail Removed)> wrote in message
>> >> news:86F2068F-F69B-4B9A-8C1F-(E-Mail Removed)...
>> >> > Alex
>> >> >
>> >> > Thanks, but I am still getting the same error, and I know it is on
>> >> > the
>> >> > Compact statement. Is it possible I am not closing the opendatabase
>> >> > properly?
>> >> > --
>> >> > Ficticiously Yours, Biggles
>> >> >
>> >> >
>> >> > "Alex Dybenko" wrote:
>> >> >
>> >> >> Hi,
>> >> >> perhaps it still opened by jet, try like this:
>> >> >>
>> >> >> FileCopy dbname, backupname
>> >> >>
>> >> >> DBEngine.CompactDatabase backupname, compactname
>> >> >>
>> >> >> FileCopy compactname, dbname
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Best regards,
>> >> >> ___________
>> >> >> Alex Dybenko (MVP)
>> >> >> http://alexdyb.blogspot.com
>> >> >> http://www.PointLtd.com
>> >> >>
>> >> >>
>> >> >> "Biggles" <(E-Mail Removed)> wrote in message
>> >> >> news:595B10A2-C69C-4DFE-A3D8-(E-Mail Removed)...
>> >> >> >I am trying to compact another database from VBA, but when I run
>> >> >> >the
>> >> >> > following code, I get this error:
>> >> >> >
>> >> >> > 3734 The database has been placed in a state by user 'me' on
>> >> >> > machine
>> >> >> > 'mymachine' that prevents it from being opened or locked.
>> >> >> >
>> >> >> > Code:
>> >> >> > Public Sub compact_delete()
>> >> >> >
>> >> >> > Dim subDBNAME As String
>> >> >> > Dim tbl As Variant
>> >> >> > Dim tbllist As Variant
>> >> >> > Dim tblbk As String
>> >> >> > Dim tblname As Variant
>> >> >> > Dim tblname2 As Variant
>> >> >> > Dim masterdb As Database
>> >> >> > Dim compactname As String
>> >> >> > Dim backupname As String
>> >> >> > Dim dbname As String
>> >> >> > 'Step .5 - Build constants
>> >> >> >
>> >> >> > On Error GoTo errhand
>> >> >> > 'File name
>> >> >> > subDBNAME = "F:\projects\Issue Track Projects\IT-001-000X-
>> >> >> > archive
>> >> >> > and
>> >> >> > others\Comerica Inc Issue Tracking Database"
>> >> >> > dbname = subDBNAME & ".mdb"
>> >> >> > compactname = subDBNAME & ".cpk"
>> >> >> > backupname = subDBNAME & ".old"
>> >> >> >
>> >> >> > 'tables to be archived
>> >> >> > tbllist = Array("[Master Recommendation Table].[Issue
>> >> >> > Reference
>> >> >> > Number]", "tblMitigatingControl.[Ref Tracking]",
>> >> >> > "tblInternalComments.[Tracking Number]", "tblPostedComments.[Ref
>> >> >> > Tracking]",
>> >> >> > "tblClientResponse.[Ref Tracking]",
>> >> >> > "tm****UETYPETABLE.txtREF_TRACK_NO")
>> >> >> >
>> >> >> > Set masterdb = OpenDatabase(dbname)
>> >> >> >
>> >> >> > 'For Each tbl In tbllist
>> >> >> > ' If InStr(tbl, " ") < InStr(tbl, ".") And InStr(tbl, " ")
>> >> >> > <>
>> >> >> > 0
>> >> >> > Then
>> >> >> > ' tbl = Mid(tbl, 2, InStr(tbl, ".") - 3)
>> >> >> > ' Else
>> >> >> > ' tbl = Mid(tbl, 1, InStr(tbl, ".") - 1)
>> >> >> > ' End If
>> >> >> > ' tblbk = tbl & "_bk"
>> >> >> > ' masterdb.TableDefs.Delete tblbk
>> >> >> > 'Next
>> >> >> >
>> >> >> > masterdb.Close
>> >> >> > Set masterdb = Nothing
>> >> >> >
>> >> >> > FileCopy dbname, backupname
>> >> >> >
>> >> >> > DBEngine.CompactDatabase dbname, compactname
>> >> >> >
>> >> >> > FileCopy compactname, dbname
>> >> >> >
>> >> >> > errhand:
>> >> >> > Debug.Print Err.Number; Err.Description
>> >> >> > End Sub
>> >> >> >
>> >> >> > I eventually want to put the delete tables back in, but won't
>> >> >> > until
>> >> >> > I
>> >> >> > get
>> >> >> > this fixed. Thanks
>> >> >> > --
>> >> >> > Yours Fictionally, Biggles
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

Ok, I think I figured it out, but it leads to another question. I noticed
that the file format was Access 2000, even though I have Access 2003
installed and the database i was trying to copy and compact is Access
2002-2003 file format. I upgraded the file and the code worked as I
expected.

Here is the new question:

I create the database where the code is running with the following statements:
Dim strDBName As String
Dim dbNew As DAO.Database

....
Set dbNew = DBEngine.Workspaces(0).CreateDatabase(strDBName,
dbLangGeneral)
Set dbNew = Nothing

What command should I be running to make sure the file created is a Access
2002-2003 format?
--
Ficticiously Yours, Biggles


"Biggles" wrote:

> Ok, I checked that by stepping through and watching the windows explorer
> window. I see the ldb file created on the Set...Opendatabase and then go
> away on the masterdb.close and set ...nothing. I am still getting the same
> error.
>
> Any other ideas? This should be the easiest part of the process I am trying
> to perform, that's why it bugs me so.
> --
> Ficticiously Yours, Biggles
>
>
> "Douglas J. Steele" wrote:
>
> > Are you certain no other connections exist to the backend (say, a bound form
> > is open)?
> >
> > Check whether the .LDB file exists (in the same folder as the .MDB you're
> > trying to compact). If it does, you haven't closed it properly. If it
> > doesn't, you should be fine.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "Biggles" <(E-Mail Removed)> wrote in message
> > news:37A62317-9012-4B72-A9E9-(E-Mail Removed)...
> > > See full code below:
> > >
> > > Set masterdb = opendatabase(dbname)
> > >
> > > do stuff (which is commented out right now)
> > >
> > > masterdb.close
> > > Set masterdb = nothing
> > > --
> > > Ficticiously Yours, Biggles
> > >
> > >
> > > "Douglas J. Steele" wrote:
> > >
> > >> What are you doing to try and close the open database?
> > >>
> > >> --
> > >> Doug Steele, Microsoft Access MVP
> > >> http://I.Am/DougSteele
> > >> (no e-mails, please!)
> > >>
> > >>
> > >> "Biggles" <(E-Mail Removed)> wrote in message
> > >> news:86F2068F-F69B-4B9A-8C1F-(E-Mail Removed)...
> > >> > Alex
> > >> >
> > >> > Thanks, but I am still getting the same error, and I know it is on the
> > >> > Compact statement. Is it possible I am not closing the opendatabase
> > >> > properly?
> > >> > --
> > >> > Ficticiously Yours, Biggles
> > >> >
> > >> >
> > >> > "Alex Dybenko" wrote:
> > >> >
> > >> >> Hi,
> > >> >> perhaps it still opened by jet, try like this:
> > >> >>
> > >> >> FileCopy dbname, backupname
> > >> >>
> > >> >> DBEngine.CompactDatabase backupname, compactname
> > >> >>
> > >> >> FileCopy compactname, dbname
> > >> >>
> > >> >>
> > >> >> --
> > >> >> Best regards,
> > >> >> ___________
> > >> >> Alex Dybenko (MVP)
> > >> >> http://alexdyb.blogspot.com
> > >> >> http://www.PointLtd.com
> > >> >>
> > >> >>
> > >> >> "Biggles" <(E-Mail Removed)> wrote in message
> > >> >> news:595B10A2-C69C-4DFE-A3D8-(E-Mail Removed)...
> > >> >> >I am trying to compact another database from VBA, but when I run the
> > >> >> > following code, I get this error:
> > >> >> >
> > >> >> > 3734 The database has been placed in a state by user 'me' on machine
> > >> >> > 'mymachine' that prevents it from being opened or locked.
> > >> >> >
> > >> >> > Code:
> > >> >> > Public Sub compact_delete()
> > >> >> >
> > >> >> > Dim subDBNAME As String
> > >> >> > Dim tbl As Variant
> > >> >> > Dim tbllist As Variant
> > >> >> > Dim tblbk As String
> > >> >> > Dim tblname As Variant
> > >> >> > Dim tblname2 As Variant
> > >> >> > Dim masterdb As Database
> > >> >> > Dim compactname As String
> > >> >> > Dim backupname As String
> > >> >> > Dim dbname As String
> > >> >> > 'Step .5 - Build constants
> > >> >> >
> > >> >> > On Error GoTo errhand
> > >> >> > 'File name
> > >> >> > subDBNAME = "F:\projects\Issue Track Projects\IT-001-000X-
> > >> >> > archive
> > >> >> > and
> > >> >> > others\Comerica Inc Issue Tracking Database"
> > >> >> > dbname = subDBNAME & ".mdb"
> > >> >> > compactname = subDBNAME & ".cpk"
> > >> >> > backupname = subDBNAME & ".old"
> > >> >> >
> > >> >> > 'tables to be archived
> > >> >> > tbllist = Array("[Master Recommendation Table].[Issue Reference
> > >> >> > Number]", "tblMitigatingControl.[Ref Tracking]",
> > >> >> > "tblInternalComments.[Tracking Number]", "tblPostedComments.[Ref
> > >> >> > Tracking]",
> > >> >> > "tblClientResponse.[Ref Tracking]",
> > >> >> > "tm****UETYPETABLE.txtREF_TRACK_NO")
> > >> >> >
> > >> >> > Set masterdb = OpenDatabase(dbname)
> > >> >> >
> > >> >> > 'For Each tbl In tbllist
> > >> >> > ' If InStr(tbl, " ") < InStr(tbl, ".") And InStr(tbl, " ") <>
> > >> >> > 0
> > >> >> > Then
> > >> >> > ' tbl = Mid(tbl, 2, InStr(tbl, ".") - 3)
> > >> >> > ' Else
> > >> >> > ' tbl = Mid(tbl, 1, InStr(tbl, ".") - 1)
> > >> >> > ' End If
> > >> >> > ' tblbk = tbl & "_bk"
> > >> >> > ' masterdb.TableDefs.Delete tblbk
> > >> >> > 'Next
> > >> >> >
> > >> >> > masterdb.Close
> > >> >> > Set masterdb = Nothing
> > >> >> >
> > >> >> > FileCopy dbname, backupname
> > >> >> >
> > >> >> > DBEngine.CompactDatabase dbname, compactname
> > >> >> >
> > >> >> > FileCopy compactname, dbname
> > >> >> >
> > >> >> > errhand:
> > >> >> > Debug.Print Err.Number; Err.Description
> > >> >> > End Sub
> > >> >> >
> > >> >> > I eventually want to put the delete tables back in, but won't until
> > >> >> > I
> > >> >> > get
> > >> >> > this fixed. Thanks
> > >> >> > --
> > >> >> > Yours Fictionally, Biggles
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

3734 The database has been placed in a state by user 'me' on machine
'mymachine' that prevents it from being opened or locked.

--
Ficticiously Yours, Biggles


"Douglas J. Steele" wrote:

> You sure about that error number? The AccessError function doesn't seem to
> have a description for 3734.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Biggles" <(E-Mail Removed)> wrote in message
> news:28AFDEFA-426D-4167-8B43-(E-Mail Removed)...
> > Ok, I checked that by stepping through and watching the windows explorer
> > window. I see the ldb file created on the Set...Opendatabase and then go
> > away on the masterdb.close and set ...nothing. I am still getting the
> > same
> > error.
> >
> > Any other ideas? This should be the easiest part of the process I am
> > trying
> > to perform, that's why it bugs me so.
> > --
> > Ficticiously Yours, Biggles
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> Are you certain no other connections exist to the backend (say, a bound
> >> form
> >> is open)?
> >>
> >> Check whether the .LDB file exists (in the same folder as the .MDB you're
> >> trying to compact). If it does, you haven't closed it properly. If it
> >> doesn't, you should be fine.
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "Biggles" <(E-Mail Removed)> wrote in message
> >> news:37A62317-9012-4B72-A9E9-(E-Mail Removed)...
> >> > See full code below:
> >> >
> >> > Set masterdb = opendatabase(dbname)
> >> >
> >> > do stuff (which is commented out right now)
> >> >
> >> > masterdb.close
> >> > Set masterdb = nothing
> >> > --
> >> > Ficticiously Yours, Biggles
> >> >
> >> >
> >> > "Douglas J. Steele" wrote:
> >> >
> >> >> What are you doing to try and close the open database?
> >> >>
> >> >> --
> >> >> Doug Steele, Microsoft Access MVP
> >> >> http://I.Am/DougSteele
> >> >> (no e-mails, please!)
> >> >>
> >> >>
> >> >> "Biggles" <(E-Mail Removed)> wrote in message
> >> >> news:86F2068F-F69B-4B9A-8C1F-(E-Mail Removed)...
> >> >> > Alex
> >> >> >
> >> >> > Thanks, but I am still getting the same error, and I know it is on
> >> >> > the
> >> >> > Compact statement. Is it possible I am not closing the opendatabase
> >> >> > properly?
> >> >> > --
> >> >> > Ficticiously Yours, Biggles
> >> >> >
> >> >> >
> >> >> > "Alex Dybenko" wrote:
> >> >> >
> >> >> >> Hi,
> >> >> >> perhaps it still opened by jet, try like this:
> >> >> >>
> >> >> >> FileCopy dbname, backupname
> >> >> >>
> >> >> >> DBEngine.CompactDatabase backupname, compactname
> >> >> >>
> >> >> >> FileCopy compactname, dbname
> >> >> >>
> >> >> >>
> >> >> >> --
> >> >> >> Best regards,
> >> >> >> ___________
> >> >> >> Alex Dybenko (MVP)
> >> >> >> http://alexdyb.blogspot.com
> >> >> >> http://www.PointLtd.com
> >> >> >>
> >> >> >>
> >> >> >> "Biggles" <(E-Mail Removed)> wrote in message
> >> >> >> news:595B10A2-C69C-4DFE-A3D8-(E-Mail Removed)...
> >> >> >> >I am trying to compact another database from VBA, but when I run
> >> >> >> >the
> >> >> >> > following code, I get this error:
> >> >> >> >
> >> >> >> > 3734 The database has been placed in a state by user 'me' on
> >> >> >> > machine
> >> >> >> > 'mymachine' that prevents it from being opened or locked.
> >> >> >> >
> >> >> >> > Code:
> >> >> >> > Public Sub compact_delete()
> >> >> >> >
> >> >> >> > Dim subDBNAME As String
> >> >> >> > Dim tbl As Variant
> >> >> >> > Dim tbllist As Variant
> >> >> >> > Dim tblbk As String
> >> >> >> > Dim tblname As Variant
> >> >> >> > Dim tblname2 As Variant
> >> >> >> > Dim masterdb As Database
> >> >> >> > Dim compactname As String
> >> >> >> > Dim backupname As String
> >> >> >> > Dim dbname As String
> >> >> >> > 'Step .5 - Build constants
> >> >> >> >
> >> >> >> > On Error GoTo errhand
> >> >> >> > 'File name
> >> >> >> > subDBNAME = "F:\projects\Issue Track Projects\IT-001-000X-
> >> >> >> > archive
> >> >> >> > and
> >> >> >> > others\Comerica Inc Issue Tracking Database"
> >> >> >> > dbname = subDBNAME & ".mdb"
> >> >> >> > compactname = subDBNAME & ".cpk"
> >> >> >> > backupname = subDBNAME & ".old"
> >> >> >> >
> >> >> >> > 'tables to be archived
> >> >> >> > tbllist = Array("[Master Recommendation Table].[Issue
> >> >> >> > Reference
> >> >> >> > Number]", "tblMitigatingControl.[Ref Tracking]",
> >> >> >> > "tblInternalComments.[Tracking Number]", "tblPostedComments.[Ref
> >> >> >> > Tracking]",
> >> >> >> > "tblClientResponse.[Ref Tracking]",
> >> >> >> > "tm****UETYPETABLE.txtREF_TRACK_NO")
> >> >> >> >
> >> >> >> > Set masterdb = OpenDatabase(dbname)
> >> >> >> >
> >> >> >> > 'For Each tbl In tbllist
> >> >> >> > ' If InStr(tbl, " ") < InStr(tbl, ".") And InStr(tbl, " ")
> >> >> >> > <>
> >> >> >> > 0
> >> >> >> > Then
> >> >> >> > ' tbl = Mid(tbl, 2, InStr(tbl, ".") - 3)
> >> >> >> > ' Else
> >> >> >> > ' tbl = Mid(tbl, 1, InStr(tbl, ".") - 1)
> >> >> >> > ' End If
> >> >> >> > ' tblbk = tbl & "_bk"
> >> >> >> > ' masterdb.TableDefs.Delete tblbk
> >> >> >> > 'Next
> >> >> >> >
> >> >> >> > masterdb.Close
> >> >> >> > Set masterdb = Nothing
> >> >> >> >
> >> >> >> > FileCopy dbname, backupname
> >> >> >> >
> >> >> >> > DBEngine.CompactDatabase dbname, compactname
> >> >> >> >
> >> >> >> > FileCopy compactname, dbname
> >> >> >> >
> >> >> >> > errhand:
> >> >> >> > Debug.Print Err.Number; Err.Description
> >> >> >> > End Sub
> >> >> >> >
> >> >> >> > I eventually want to put the delete tables back in, but won't
> >> >> >> > until
> >> >> >> > I
> >> >> >> > get
> >> >> >> > this fixed. Thanks
> >> >> >> > --
> >> >> >> > Yours Fictionally, Biggles
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>