Createdatabase (Save to a folder, name, import tables)

[CWH via AccessMonster.com]

Hello;

Wondering if someone could help me stop pulling my hair out…. I am trying to
programmatically create a new database. When a user clicks on a command
button I would like the user to select a folder where to place the new
database. VB would then name the new database RMS – “The Users Name†(the
contents of a drop down comboBox “Team Nameâ€), then the current date.
Secondly I would like to then import tables into the new database – Although
I haven’t gotten past start yet….

Example: RMS – John Doe, 02112007

This is what I have so far. It opens a browse folder and allows me to select
a folder and create a new data file. Its not creating a .mdb file, or
grabbing the field contents of the combobox: Team Name.

Sub CreateNewMDBFile_Click()
Dim ws As Workspace
Dim db As Database
Dim dbBackup As DAO.Database
Dim strFile As String
Dim strFolderName As String

Set ws = DBEngine.Workspaces(0)
strFolderName = BrowseFolder("Where would you like to save your records?")

Set dbBackup = ws.CreateDatabase("RMS- (ComboBox:Team Name],(" & Format
(Date, "mmddyyyy") & ").mdb", dbLangGeneral)

Any help would be appreciated.

Thanks

 

[John W. Vinson]

Hello;

Wondering if someone could help me stop pulling my hair out…. I am trying to
programmatically create a new database. When a user clicks on a command
button I would like the user to select a folder where to place the new
database. VB would then name the new database RMS – “The Users Name” (the
contents of a drop down comboBox “Team Name”), then the current date.
Secondly I would like to then import tables into the new database – Although
I haven’t gotten past start yet….

Example: RMS – John Doe, 02112007

Well... I REALLY have to question the logic of this. Storing data - a
name, a date - in a *DATABASE NAME* seems like bad design. Why? What
will be done with this database? If it's for backup, you're really
best off backing up the ENTIRE database rather than having separate
personal (partial?) backups.

That said... what you'll need to do is build up a text string from
pieces. Assuming that you have a Combo Box on the form bound to the
Team Name field, I'd suggest renaming it; change its Name property to
(say) cboTeamName, but leave the Control Source alone. You can then
use the code below.

This is what I have so far. It opens a browse folder and allows me to select
a folder and create a new data file. Its not creating a .mdb file, or
grabbing the field contents of the combobox: Team Name.

Sub CreateNewMDBFile_Click()
Dim ws As Workspace
Dim db As Database
Dim dbBackup As DAO.Database
Dim strFile As String
Dim strFolderName As String

Set ws = DBEngine.Workspaces(0)
strFolderName = BrowseFolder("Where would you like to save your records?")

strFile = "RMS - " & Me.cboTeamName & Format(Date, "mmddyyyy") _
& ".mdb"
Set dbBackup = ws.CreateDatabase(strFile, dbLangGeneral)

This would be followed by something like calls to TransferDatabase to
transfer (empty) tables from a template database, then to populate
those tables by running Append queries as appropriate.

John W. Vinson [MVP]

 

[CWH via AccessMonster.com]

Hi John;

Yea I agree, somewhat primitive here. This User database is large so I’m
trying to provide a mechanism of downloading just select tables/records. They
are being downloaded to the transfer.mdb database, which in turn is uploaded
to a backup Admin.mdb system. I have several people using there own copy of
the User database and weekly download to a transfer.mdb database. Each user
submits (emails, USB, or CD) his/her own transfer.mdb to the supervisor who
uploads them into the admin.mdb (make sense?). We don’t have a network or
direct connect between the User databases and the Admin.mdb database. The
transfers.mdb acts as link between the two. Since I have several people
submitting copies of the transfer.mdb I thought to add there names and the
date it was created so the supervisor can get a better handle on several
submissions.

I will also have to change the VB code in the Admin.mdb to accept & append
tables from different database files.

Thanks for reviewing the code. I tried implementing it but I’m getting a
runtime error 438. Strange since I have access installed… “Object doesn’t
support this property or method. This is what I have so far; Thanks a lot.

Sub cboTeamName_Click()
Dim ws As Workspace
Dim db As Database
Dim dbBackup As DAO.Database
Dim strFile As String
Dim strFolderName As String
'Get default Workspace
Set ws = DBEngine.Workspaces(0)
strFolderName = BrowseFolder("Where would you like to save your records?")

strFile = "RMS - " & Me.cboTeamName & Format(Date, "mmddyyyy") & ".mdb"
Set dbBackup = ws.CreateDatabase(strFile, dbLangGeneral)


End Sub

 

[John W. Vinson]

Hi John;

Yea I agree, somewhat primitive here. This User database is large so I’m
trying to provide a mechanism of downloading just select tables/records. They
are being downloaded to the transfer.mdb database, which in turn is uploaded
to a backup Admin.mdb system. I have several people using there own copy of
the User database and weekly download to a transfer.mdb database. Each user
submits (emails, USB, or CD) his/her own transfer.mdb to the supervisor who
uploads them into the admin.mdb (make sense?). We don’t have a network or
direct connect between the User databases and the Admin.mdb database. The
transfers.mdb acts as link between the two. Since I have several people
submitting copies of the transfer.mdb I thought to add there names and the
date it was created so the supervisor can get a better handle on several
submissions.

Sounds like you're "rolling your own" Replication. Access has a built
in Replication model - but it's quite complex and requires a lot of
study and work to implement. It may be worth checking into; maybe you
could download the Replication Whitepaper from

http://support.microsoft.com/kb/282977/en-us

It's for A2000 but works in 2002 and 2003; not sure about 2007!

STUDY IT CAREFULLY, and try it out on a COPY of your database -
replication makes major and irreversible changes to your database,
such as adding four new fields per table, making all your Autonumbers
become random, and more.

I will also have to change the VB code in the Admin.mdb to accept & append
tables from different database files.

Thanks for reviewing the code. I tried implementing it but I’m getting a
runtime error 438. Strange since I have access installed… “Object doesn’t
support this property or method. This is what I have so far; Thanks a lot.

Sub cboTeamName_Click()
Dim ws As Workspace
Dim db As Database
Dim dbBackup As DAO.Database
Dim strFile As String
Dim strFolderName As String
'Get default Workspace
Set ws = DBEngine.Workspaces(0)
strFolderName = BrowseFolder("Where would you like to save your records?")

strFile = "RMS - " & Me.cboTeamName & Format(Date, "mmddyyyy") & ".mdb"
Set dbBackup = ws.CreateDatabase(strFile, dbLangGeneral)

What is the value of strFile? What line generates the error? Check
Tools... References and make sure Microsoft Data Access Objects x.xx
(highest version) is checked and that none are marked MISSING.

John W. Vinson [MVP]

 

[CWH via AccessMonster.com]

Hi John;

[quoted text clipped - 10 lines]

date it was created so the supervisor can get a better handle on several
submissions.

Sounds like you're "rolling your own" Replication. Access has a built
in Replication model - but it's quite complex and requires a lot of
study and work to implement. It may be worth checking into; maybe you
could download the Replication Whitepaper from

http://support.microsoft.com/kb/282977/en-us

It's for A2000 but works in 2002 and 2003; not sure about 2007!

STUDY IT CAREFULLY, and try it out on a COPY of your database -
replication makes major and irreversible changes to your database,
such as adding four new fields per table, making all your Autonumbers
become random, and more.

I will also have to change the VB code in the Admin.mdb to accept & append
tables from different database files.

[quoted text clipped - 15 lines]

strFile = "RMS - " & Me.cboTeamName & Format(Date, "mmddyyyy") & ".mdb"
Set dbBackup = ws.CreateDatabase(strFile, dbLangGeneral)

What is the value of strFile? What line generates the error? Check
Tools... References and make sure Microsoft Data Access Objects x.xx
(highest version) is checked and that none are marked MISSING.

John W. Vinson [MVP]

ah.. Thanks John

Doubled checked and had an error in the cboTeamName field grrrr....
I'm going to bug you one more time. The code opens the browse folder & I'm
allowed to select a folder, however once the new database is created it stays
within the same folder the current database resides in. The code is not
allowing it to be created in any other folder...

 

[John W. Vinson]

The code opens the browse folder & I'm
allowed to select a folder, however once the new database is created it stays
within the same folder the current database resides in. The code is not
allowing it to be created in any other folder...

You'll need to create the entire path and filename, then - not just
the filename.

John W. Vinson [MVP]

 

[CWH via AccessMonster.com]

You'll need to create the entire path and filename, then - not just
the filename.

John W. Vinson [MVP]

Thanks John;

Do you know if it can be written into the code?. It's creating the database
fine. All I want the user to do is select the location to place it.

 

[John W. Vinson]

Do you know if it can be written into the code?. It's creating the database
fine. All I want the user to do is select the location to place it.

ummmm... sure. You already DID... strFolderName. Just concatenate it
into the value of strFile:

strFile = strFolderName & "RMS - " & Me.cboTeamName _
& Format(Date, "mmddyyyy") & ".mdb"

Since I don't have a function named GetFolder on my database, I'm not
certain what it returns - you might need to include a \ character as a
delimiter, and perhaps some quotemarks (Chr(34) is a " character)
before and after the string.

John W. Vinson [MVP]

 

[CWH via AccessMonster.com]

ummmm... sure. You already DID... strFolderName. Just concatenate it
into the value of strFile:

strFile = strFolderName & "RMS - " & Me.cboTeamName _
& Format(Date, "mmddyyyy") & ".mdb"

Since I don't have a function named GetFolder on my database, I'm not
certain what it returns - you might need to include a \ character as a
delimiter, and perhaps some quotemarks (Chr(34) is a " character)
before and after the string.

John W. Vinson [MVP]

Hey John:

Thanks works perfectly. That's why you are a MVP and I am just a want-a-be....

 

[CWH via AccessMonster.com]

[quoted text clipped - 11 lines]

John W. Vinson [MVP]

Hey John:

Thanks works perfectly. That's why you are a MVP and I am just a want-a-be....

Working with the Admin.mdb now. I have a group of tables (checkbox =
true/false) that can be imported using a single command button from the
transfer.mdb.

If Check207 = True Then
DoCmd.TransferDatabase acImport, "Microsoft Access", strInputFileName,
acTable, "Detection", "Detection"

next table....

next table...etc...

No problem importing until the user accidentially selects a table to import
that isn't actually in the transfer.mdb. I receive runtime error 3011. I can
build a general function error msg but would perfer that if the table the
user tries to import isn't present, the code moves onto the next table in
line.

Any help would be greatly appreicated.

 

[John W. Vinson]

No problem importing until the user accidentially selects a table to import
that isn't actually in the transfer.mdb. I receive runtime error 3011. I can
build a general function error msg but would perfer that if the table the
user tries to import isn't present, the code moves onto the next table in
line.

You can use a specific error trap:

On Error GoTo Proc_Error
<your code here>
Proc_Exit:
Exit Sub
Proc_Error:
Select Case Err.Number
Case 3011
Resume Next
Case Else
MsgBox "Error " & Err.Number & " in <subroutine name>" _
& vbCrLf & Err.Description
Resume Proc_Exit
End Sub

John W. Vinson [MVP]