Problems with compacting

[Guest]

Hi,

I have a database that is approximately 1.63GB (as per properties). Windows
Explorer shows it as 1.77GB.

When I try to compact it I get the "invalid Argument" error. It contains a
few tables and these have minimal data (1 or 2 rows) in them. One table has
420,000 records and 250 feilds in it. Even if I delete the contents of the
table I still get the same error.

I may be able to change my design so the number of columns aren't as large
but I really want to know why I can't compact it????

Also, how do I create a copy of a Database programatically. Currently I can
do it manually by Creating a blank database and the File - Get External Data
........

I reckon I can write code to identify all tables in the file to copy but can
you please help with how I nominate whether I want to import the data or
definition only.

 

[Pieter Wijnen]

Something like

Sub ImpDef()
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim TDef As DAO.TableDef
Dim thQ As String
Set Db = DBEngine.Workspaces(0).OpenDatabase("C:\MyDb.mdb")
thQ = "SELECT Name From MSysObjects O " & _
"WHERE Type=1 " & _
"AND Name Not Like 'MSys*'"
Set Rs = Db.OpenRecordset(DAO.dbOpenSnapshot)
While Not Rs.EOF
Set TDef = Db.TableDefs(Rs.Fields(0).Value)
DoCmd.TransferDatabase acImport, "Microsoft Access", Db.Name, acTable,
TDef.Name, TDef.Name, True
Rs.MoveNext
Wend
Rs.Close: Set Rs = Nothing
Db.Close: Set Db = Nothing
End Sub

HTH

Pieter

 

[Guest]

Hi Pieter,

I've adapted your code to.......

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim TDef As DAO.TableDef
Dim thQ As String
Set Db =
DBEngine.Workspaces(0).OpenDatabase("G:\06_ip\Andrew\WIP\Purchase order
listing\Purchase order listing_Part_1.mdb")
thQ = "SELECT Name From MSysObjects O WHERE Type=1 AND Name Not Like
'MSys*'"
Set Rs = Db.OpenRecordset(DAO.dbOpenSnapshot)
While Not Rs.EOF
Set TDef = Db.TableDefs(Rs.Fields(0).Value)
DoCmd.TransferDatabase acImport, "Microsoft Access", Db.Name,
acTable, TDef.Name, TDef.Name, True
Rs.MoveNext
Wend
Rs.Close: Set Rs = Nothing
Db.Close: Set Db = Nothing


I keep getting an error


.......can't find input table or query '4'....


Any ideas?

 

[Pieter Wijnen]

After all your old db is a bit corrupted!
You can exclude the offending table from the select statement
thQ = "SELECT Name From MSysObjects O WHERE Type=1 AND Name Not Like
'MSys*' And Name Not In ('4')"

or simply add 'On Error Resume next' as the error handler (if it happens on
the Transferdatabase line)
you can also try to run
DoCmd.DeleteObject acTable, "4"
in the backend

HTH

Pieter

 

[Guest]

Hi Pieter,

Tried options 1 and 2 and I get an error saying can't find table or query
called '4' (and I don't). When I step through (after Resume Next). It errors
'Object vaiable not set......'

Sorry if I'm missing something obvious.
..

 

[Pieter Wijnen]

Typo by me (yet again)

Set Rs = Db.OpenRecordset(thQ, DAO.dbOpenSnapshot)

Pieter

 

[Guest]

Hi Pieter,

Thanks, that worked!.

Just a couple of queries. I also want to copy the MSysIMEXSpecs &
MSysIMEXColumns tables, queries, forms and reports as well. I imagine I'll
need to alter the following piece of code:

thQ = "SELECT Name From MSysObjects O WHERE Type=1 AND Name Not Like 'MSys*'
And Name Not In ('4')"

but I'm not sure of the exact code. Does Type=1 mean tables only? And what
does 'Name Not In ('4') mean?

Also, to make it just that little more challenging for you is it possible to
copy the original DB ("G:\06_ip\Andrew\WIP\Purchase order listing\Purchase
order listing_Part_1.mdb") into a new one. Currently it adds the tables to
this one where the code to copy is - I want the code to create a new one -
"G:\06_ip\Andrew\WIP\Purchase order listing\Purchase order
listing_Part_1_COPY.mdb")

I hope after this I'll be able to leave you alone.

 

[Pieter Wijnen]

MSysIMEXSpecs & MSysIMEXColumns can indeed be copied too
by ammending the SQL
the Name Not In ('4') is a remnant from my typo

Type=1 does indeed refer to local tables

I will put the complete code to make your own import/export (add-in) on my
website as soon as I get around to setting it up (useful when you need to
copy from one secured db to another). In the mean time you have to make do
with the standard import/export - or do some work yourself <g>

Pieter

look out for http://www.wijnen.no , it might be useful soon <g>