You are here:

Using MS Access/CreateDatebase with Password

Advertisement


Question
QUESTION: How can I create a database with a password?

Thus Far:

Variables:

sFolder = “A:MainOutput”
sDatabaseName = “test13.accdb
sPass = “12356

This works: 1 line across no continuation          

Set accessApp = New Access.Application
accessApp.DBEngine.CreateDatabase sFolder & "  " &  sDatabaseName, dbLangGeneral

This does not work: 1 line across no continuation     

accessApp.DBEngine.CreateDatabase sFolder & "  " & sDatabaseName, dbLangGeneral, ";pwd=" & sPass  & ""

Error Message: Datatype Conversion Error

Taken Directly from Microsoft at:

http://msdn.microsoft.com/en-us/library/bb243161(v=office.12).aspx

ANSWER: Try it this way:

accessApp.DBEngine.CreateDatabase(sFolder & "  " & sDatabaseName, dbLangGeneral & ";pwd='" & sPass  & "'")

I believe password is a text datatype Pluse it nees to be concatenated with the locale.


Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Yes, your answer works, and I will follow through with a Thank you.

Here is another part of my code. Here are the facts:

1. I would like to have the database open 1 time and export all 23 tables.
2 The database opens and ask for the password. This works.
3. After entering the password, it is only exporting 1 to 3   
  tables instead of 23 tables.
4. It will stop and ask for the password over and over gain.
5. When the code gets to here:

DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDB, _
        acTable, AnArray(i), AnArray(i), True

it starts exporting the tables, but ask for the password over and over again.

I would like to have the database open 1 time and export all 23 tables.

Here is the code:

'Create Array to Hold Table Names  - 23 tables
'Set the Folder and Database Location

        Set DB = CurrentDb()
        Set RS = DB.OpenRecordset("ARES_TABLES")
         
        Count = 0
        ReDim Preserve AnArray(0)
         
        RS.MoveFirst          ' Fill the array.
        
        Do Until RS.EOF
         
        AnArray(Count) = RS![TABLE_NAME]   ' Fill the array with
         ' table names.
         
        ReDim Preserve AnArray(UBound(AnArray) + 1)
         
        Count = Count + 1
         
        RS.MoveNext
        
        Loop
         
        ReDim Preserve AnArray(UBound(AnArray) - 1)
        
        RS.Close
         
'For each table name in the Array - Export the Tables to the New Database

   
        For i = 0 To Count - 1
        
        DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDB, _
        acTable, AnArray(i), AnArray(i), True

        Next i
         
    accessApp.Quit
    Set accessApp = Nothing

Any solutions - thank you in advance

Answer
Why bother with the array? You already have the names of the tables in a tble. Why not just loop through the recordset:

'Create Array to Hold Table Names  - 23 tables
'Set the Folder and Database Location

       Set DB = CurrentDb()
       Set RS = DB.OpenRecordset("ARES_TABLES")
        
       Count = 0
       ReDim Preserve AnArray(0)
        
       RS.MoveFirst          ' Fill the array.
       
       Do Until RS.EOF
       
       DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDB, _
       acTable, RS.Fields("tablename"), RS.Fields("DestFolder"), True

        
       RS.MoveNext
       
       Loop
        
       
       RS.Close
        
        
   accessApp.Quit
   Set accessApp = Nothing


See if this works better

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.