3734 Error when trying to compact database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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]", "tmpISSUETYPETABLE.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
 
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 said:
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 said:
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]", "tmpISSUETYPETABLE.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
Click to expand...
Click to expand...
 
What are you doing to try and close the open database?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Biggles said:
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 said:
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 said:
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]",
"tmpISSUETYPETABLE.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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
What are you doing to try and close the open database?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Biggles said:
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 said:
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


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]",
"tmpISSUETYPETABLE.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
Click to expand...
Click to expand...
Click to expand...
 
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

(no e-mails, please!)


Biggles said:
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 said:
What are you doing to try and close the open database?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Biggles said:
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


:

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


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]",
"tmpISSUETYPETABLE.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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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

(no e-mails, please!)


Biggles said:
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 said:
What are you doing to try and close the open database?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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


:

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


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]",
"tmpISSUETYPETABLE.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
Click to expand...
Click to expand...
Click to expand...
 
You sure about that error number? The AccessError function doesn't seem to
have a description for 3734.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Biggles said:
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 said:
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

(no e-mails, please!)


Biggles said:
See full code below:

Set masterdb = opendatabase(dbname)

do stuff (which is commented out right now)

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


:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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


:

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


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]",
"tmpISSUETYPETABLE.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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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

(no e-mails, please!)


Biggles said:
See full code below:

Set masterdb = opendatabase(dbname)

do stuff (which is commented out right now)

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


:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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


:

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


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]",
"tmpISSUETYPETABLE.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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
You sure about that error number? The AccessError function doesn't seem to
have a description for 3734.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Biggles said:
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 said:
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

(no e-mails, please!)


See full code below:

Set masterdb = opendatabase(dbname)

do stuff (which is commented out right now)

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


:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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


:

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


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]",
"tmpISSUETYPETABLE.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
Click to expand...
Click to expand...
Click to expand...
 
Take a look at
http://groups.google.com/group/micr...e+format"++2003&rnum=2&hl=en#bc8b6ef34467f305

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Biggles said:
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 said:
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 said:
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

(no e-mails, please!)


See full code below:

Set masterdb = opendatabase(dbname)

do stuff (which is commented out right now)

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


:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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


:

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


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]",
"tmpISSUETYPETABLE.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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top