saving data from form to new table/new mdb

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

Guest

using VBA I want to save data recently entered into a form to a new table/new
mdb. "DoCmd.TransferDatabase acExport," does not work for me because I want a
new mdb created at the time of save.

thanks!
 
The folks in this newsgroup might be able to offer a reasonable alternative
approach if you provide a bit more explanation of why you want to do this.
What business need are you attempting to meet by creating a new database
file?

More info, please...

Jeff Boyce
<Access MVP>
 
Here is a function that will create a new Jet 4.0 (Access 2K) database. Put
it in a Module and call it with the full filespec for the new database. It
returns the same full filespec passed on success, and with a formatted Error
number and Error Description on failure.

Public Function MakeNewDB(strFullFileSpec) As String
Dim cat As ADOX.Catalog

On Error GoTo ErrMakeNewDB
Set cat = New ADOX.Catalog
cat.Create ("Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFullFileSpec)
MakeNewDB = strFullFileSpec
ErrMakeNewDBOut:
Set cat = Nothing
Exit Function
ErrMakeNewDB:
MakeNewDB = "Error Number: " & Err.Number & vbCrLf _
& "Error Descr: " & Err.Description
Resume ErrMakeNewDBOut
End Function


Ron W
www.WorksRite.com
 
For new bill of material, purchasing expects a .MDB at the NEWBOM folder on
our network. This .MDB needs to be named the job id and have a table in it
with items to be purchased.
My form has a save command button that will send a table to that NEWBOM
folder and append that same data to a pick file referenced by combo boxes of
items previously ordered.
I thought of having a blank .MDB, transfering the data from the form into it
as table, and then having it moved to the NEWBOM folder but not sure how to
make VBA do that.

Thanks!
 
Thanks for your response but I do not understand enough to make it work with
my form save command button. The form name is "BOM" and the location to save
data "F:\NEWBOM\" as "job id.MDB" I expect I need to put the location and
name in your function???

Thanks Again!!!
 
WB

Paste the function into the form of a module in your app then call the
function with the full filespec for the database you want to create. Here
is some "Air Code" that you would put under the button click event that
creates the database

dim strRetVal as String, strNewDBName as String
strNewDBName = "c:\foo\bar.mdb"
strRetVal = MakeNewDB(strNewDBName )
if strRetVal <> strNewDBName then
msgbox "Error creating database " & strNewDBName _
& vbcrlf & strRetVal
else
msgbox "Successfully created " & strNewDBName
end if

Ron Weiner
www.WorksRite.com
 
I appreciate the reply.
I am going to work with it later today but after pasting and a quick test I
get an err : "User-defined type not defined" referring to "Dim cat As
ADOX.Catalog".
Once again many thanks!
 
Ooooppppsss... Shoulda' mentioned that you need to add a reference to the
ADOX extensions. Sorry:-(

You need to add a reference to the "Microsoft ADO Ext. 2.x for DDL and
Security" From Access Hit <Alt>F11 then from the VB window go to Tools |
References and add the reference above. Click OK to save the settings, and
you should be good to go.

Ron W
www.WorksRite.com
 
All I can say is WOW! This is cool. WORKS GREAT as you knew it would.
I use "DoCmd.TransferDatabase acExport, ...." after your code and there it is.
You've been a great help! I never would have known.
Thanks Again!!!
 
If I have'nt worn you out yet, how would I create a "save as" or "export to"
dialog box so that I can search the network for the location to place this
file?
 
Back
Top