Exporting Table As Database

  • Thread starter Thread starter LarryP
  • Start date Start date
L

LarryP

I have an audit DB with information we don't want most
people to be able to edit; we have that worked out with a
combination of Access and network workgroup tricks that
let them look-but-not-touch.

THE PLOT THICKENS: After people open this database and
look at their audit results on-screen, we've provided a
way for them to export their results to a separate
database where they can input their responses. We have
this working fine by (1) exporting the working table to a
template database, overwriting the same-name table that's
already there, and then (2) copying the template database
(which now contains their "stuff") to their local c:\
drive.

PROBLEM: People being people, users were finding the
template database file on the network, opening it, and
trying to use it to input their responses, even though the
information it contained might not be theirs at all. For
the moment we've beaten that problem by storing that file
in an obscure corner of our network where they're unlikely
to stumble across it unless they're clued in to the Access
linked table manager, etc.

SO FINALLY, MY QUESTION: Anybody know a way to export a
table directly to a totally new, code-created database
file, so we can dispense with the intermediate "template"
part of this? I've thought about it a bit, but can't get
around the fact that the newly created DB would have to
have a compatible TableDef to receive the contents of the
working table. Any help welcome.
 
This code creates a new database, and turns off the properties that are
likely to corrupt it.

You can then execute a MakeTable query with an IN clause to write your table
into the new database.

Sub CreateDatabaseDAO()
Dim dbNew As DAO.Database
Dim prp As DAO.Property
Dim strFile As String

'Create the new database.
strFile = "C:\SampleDAO.mdb"
Set dbNew = DBEngine(0).CreateDatabase(strFile, dbLangGeneral)

'Create example properties in new database.
With dbNew
Set prp = .CreateProperty("Perform Name AutoCorrect", dbLong, 0)
.Properties.Append prp
Set prp = .CreateProperty("Track Name AutoCorrect Info", _
dbLong, 0)
.Properties.Append prp
End With

'Clean up.
dbNew.Close
Set prp = Nothing
Set dbNew = Nothing
Debug.Print "Created " & strFile
End Sub
 
Great, Allen, thanks. That should get me started in the
right direction. After I posted my first message in this
thread I realized the new DB is also going to have to have
one query and one form in addition to the table, but I'll
see if I can figure that out on my own before troubling
you further. Cheers!
 
This did indeed solve my problem, but on the road to
success something interesting happened that I don't
completely understand.

After some experimenting, what I wound up doing was
creating the new database as you recommended, then
exporting into it the table, form, report, and AutoExec
macro to make it do what it's supposed to.

However, when I tried to do all that with VBA code (the
DoCmd.ExportDatabase method), it failed abruptly every
time -- no useful error messages, just "Sorry, we just had
a huge problem and must now shut down." The only way I
finally got it done was by putting the EXACT SAME STUFF in
a Macro and then using DoCmd.RunMacro.

As far as my original problem, "My work here is done," but
if anyone can provide enlightenment about why a Macro
works when apparently identical VBA code doesn't, I'd love
to hear it. At the moment it sounds like another TGP
(Typical Gates Production) to me.
 
Hi Larry

There is a chance that your database is partically corrupt. Assuming Access
2000 or later, try this sequence:

1. Turn off the Name AutoCorrect check boxes under:
Tools | Options | General
Then compact the database to get rid of this stuff:
Tools | Database Utilities | Compact
For an explanation of why, see:
http://allenbrowne.com/bug-03.html

2. Decompile a copy of the database, by entering something like this at the
command prompt while Access is not running. It's one line, and include the
quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
The compact again.

3. Deselect any references you don't need. You may be able to get it back to
just 3. Details:
http://allenbrowne.com/ser-38.html

4. Make sure your code compiles. From the code window, choose Compile from
the Debug menu.

5. Make sure you have the latest service back for both your version of
Office and also for Jet 4. You can find both in the Downloads section at
http://support.microsoft.com

That's the crucial stuff. For other suggestions on avoiding corruption while
developing, see:
http://allenbrowne.com/ser-25.html
 
Back
Top