One Click to Create a New Database

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

Guest

I have an Access 2003 database (All Employees.mdb) that I’m working on. It
has a table (Employee List) with 3 fields (Name, User Name, Password) and a
record for each employee in the office. There is also a form (Employee Info)
which displays this information for the user. On the form is a button
(Create Profile).

What I am trying to accomplish using VBA/ADO is that when the user clicks
the Create Profile command button from the Employee Info form Access creates
a new database (Employee Profile.mdb) in a different folder (C:\profile\)
with a single table (User Profile) with the same 3 fields as the Employee
List table (Name, User Name, Password) but containing only the current record
selected/displayed on the Employee Info form at the time the button is
clicked. I don't want it to open the new database nor do I want it to close
the one that is open.

I've included all the important names (I think) in hopes someone can provide
me with some sample starter code. Any ideas?
 
Hi Michael,

There aren't all that many problems for which the answer is a
proliferation of little databases, so I'm far from confident in what
you're doing. But here's an example sample of how to do it.

Because spaces and special characters in object names always complicate
coding and sometimes cause worse problems, I've removed them (e.g.
EmployeeList instead of Employee List).

Private Sub cmdCreateProfile_Click()
Dim D As DAO.Database
Dim strDBName As String
Dim strSQL As String

strDBName = "C:\profile\Employee Profile.mdb"
Set D = DBEngine(0).CreateDatabase(strDBName, dbLangGeneral)
D.Close

'build and execue SQL statement for MakeTable query to
create tablein new database with the desired record

strSQL = "SELECT * INTO EmployeeList IN '" & strDBName _
& "' FROM AddrNew WHERE UserName = ' _
& Me.UserName.Value & "';"
CurrentDb.Execute strSQL, dbFailOnError
End Sub
 
Back
Top