Create New Database in various paths

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

Guest

I need to create a new blank database using code....which I know how to do to
the same path each time. What I need is to be able to browse to a new path
instead of always using the same one. Some users have a floppy drive (which
is what I've been using with the path being a:\database.mdb. Now most users
have memory sticks (which use the next available drive letter) or cd writers,
so I need for them to be able to select where they want to create the new
database.

Is there a way to have a "browse" button that will open explorer and let
them browse to the location and use that as the path to the new database?
Thanks!!
Susan
 
'Copy and paste the following code into a new module and then run
CreateDatabase
'________________________________________________________
'________________________________________________________
Option Compare Database
'________________________________________________________
'________________________________________________________
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
'________________________________________________________
'________________________________________________________
Private Declare Function SHBrowseForFolder _
Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long
'________________________________________________________
'________________________________________________________
Private Declare Function SHGetPathFromIDList _
Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long
'________________________________________________________
'________________________________________________________
Public Function GetFolder(Optional Title As String, _
Optional hWnd) As String

Dim bi As BROWSEINFO
Dim pidl As Long
Dim folder As String

folder = String$(255, Chr$(0))

With bi

If IsNumeric(hWnd) Then .hOwner = hWnd
.ulFlags = BIF_RETURNONLYFSDIRS
.pidlRoot = 0
If Title <> "" Then
.lpszTitle = Title & Chr$(0)
Else
.lpszTitle = "Select a Folder" & Chr$(0)
End If
End With

pidl = SHBrowseForFolder(bi)

If SHGetPathFromIDList(ByVal pidl, ByVal folder) Then
GetFolder = Left(folder, InStr(folder, _
Chr$(0)) - 1)
Else
GetFolder = ""
End If

End Function
'_______________________________________________________________
'_______________________________________________________________
Sub CreateDatabase()
Call NewDB(GetFolder("Where do you want to save the new database?"),
InputBox("What do you want to call the new database?", "New Database Name:")
& ".mdb")
End Sub
'_______________________________________________________________
'_______________________________________________________________
Function NewDB(dbPath As String, dbName As String)

Dim wrkDefault As Workspace
Dim dbsNew As Database
Dim prpLoop As Property

Set wrkDefault = DBEngine.Workspaces(0)
ChDir (dbPath)
If Dir(dbName) <> "" Then Kill dbName
Set dbsNew = wrkDefault.CreateDatabase(dbName, dbLangGeneral)
dbsNew.Close

End Function
'_______________________________________________________________
'_______________________________________________________________
 
Thanks!
This is the code I was using to create the database on a floppy disk:

'CreateDatabase Method Example

'This example uses CreateDatabase to create a new, encrypted Database object.

Public Sub CreateDatabaseX()

Dim wrkDefault As Workspace
Dim dbsNew As Database
Dim prpLoop As Property

' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)

' Make sure there isn't already a file with the name of
' the new database.
If Dir("a:\Data3D.mdb") <> "" Then Kill "a:\Data3D.mdb"

' Create a new NOT encrypted database with the specified
' collating order. (Susan took out encryption 3/26/03 & named)
Set dbsNew = wrkDefault.CreateDatabase("a:\Data3D.mdb", _
dbLangGeneral) ' (Susan took out ", dbEncrypt)" just inside the )


dbsNew.Close

End Sub

Function Create()
CreateDatabaseX
End Function

Can you tell me how to incorporate this new code that opens the "Browse"
dialog box at the proper time to create the new database in the folder the
user chooses?
Thanks again!!
Susan
 
Are you saying you always want the database to be named Data3D.mdb?

Public Sub CreateDatabaseX()

Dim wrkDefault As Workspace
Dim dbsNew As DAO.Database
Dim strCompletePath As String
Dim strFile As String
Dim strFolder As String

strFile = "Data3D.mdb"

' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)

' Prompt the user for a folder
' (Requires the code from http://www.mvps.org/access/api/api0002.htm to
be
' saved into a module in the application)
strFolder = BrowseFolder("Where do you want the backup file to be put?"
If Right$(strFolder, 1) <> "\" Then
strFolder = strFolder & "\"
End If
strCompletePath = strFolder & strFile

' Make sure there isn't already a file with the name of
' the new database.
If Dir(strCompletePath) <> "" Then Kill strCompletePath

' Create a new NOT encrypted database with the specified
' collating order. (Susan took out encryption 3/26/03 & named)
Set dbsNew = wrkDefault.CreateDatabase(strCompletePath, _
dbLangGeneral)

dbsNew.Close

End Sub
 
Now I want to include some tables in the newly created database. Not all the
tables from the database where I'm running the code, only some of them. Is
this possible? If so, can you help me with how to do it, please?
Thanks!
Susan
 
OK, sorry to be so "dense", but I see how this works if I know the path to
the new database and can put that in the code, but How do I know what to put
for the "Destination Database" if I don't know the path the user selected for
where to create the new database? I'm thinking there may be something to
tell it to use the new database just created (wherever it is) and export them
to it, but I don't know how!

Thanks again!
Susan
 
You know where the user created the new database: the full path to it is
stored in strCompletePath.
 
Back
Top