Create a Jet db on the fly

JohnPapa

Registered User.
Local time
Today, 16:41
Joined
Aug 15, 2010
Messages
1,120
In my application I create Jet dbs on the fly and subsequently add the necessary fields. Although I use predominantly ADO for my coding, I use DAO to create the db with no problems, as indicated below,

Set db = DBEngine.CreateDatabase(DBFullPath, dbLangGeneral)

I have 2 questions:
1) Can I add a password to the db created with DAO?
2) Can I use ADOX to create the dB? I tried using the following

Dim cat As New ADOX.Catalog
Dim strFile As String
cat.Create "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source='" & strFile & "'"
Set cat = Nothing

It creates the db, but when I try to open the db by clicking on the db file, it tells me
"Record(s) cannot be read; no read permission on 'MSysObjects' ". Apparently I have no read permission.

I used ADOX in the past and it proved unstable and version dependent.

Essentially, I would like to create on the fly a password-protected database and be able to add/delete fields and data. Any ideas?

Thanks,
John
 
Thanks for the reply. I believe I had a look at what you kindly sent over, but could not find how to create a Password-protected db with ADO.

Also, is there a way to create a Password-protected db using DAO?

Thanks,
John
 
With DAO it's easy.

db.NewPassword "","NewPassWordHere"
 
Thanks,

db.NewPassword "","NewPassWordHere"

is the command I was looking for. DAO was indeed specifically made for Access and that is why people still use it.

Can't ADO create a blank Password-protected db? I am sure it can.

Since the rest of my code is in ADO, I just now need to find out how, I OPEN with ADO a Password-protected db.

Thanks,
John
 
DJkarl,

I am trying first the DAO route.I use A03 and I am creating dbs from within my main db.

db.NewPassword works fine

I try to open the db using

Set db = OpenDatabase(strDBPathname, True, False, ";pwd=" & pstrPassword)

Sometimes, it asks me to enter the db Password. I want it to open db without asking me, as long as the correct Password is used.

Any ideas why it sometimes asks me for the Password?
 
When I use DAO to open a PWD protected DB I use the following.

OpenDatabase(dbName, True, False, "MS Access;pwd=MYPASSWORD")

It will work without the MS Access...but I think I had it in there for a reason.
 
On further research the problem does not appear on Opening the db but on trying to create a link to the table inside the DB,


DoCmd.TransferDatabase acLink, "Microsoft Access", "database=" & strDBPathname & ";pwd=" & pstrPassword, acTable, strTableName, strTableName


The error mesage is "3043Disk or Network Error"

I will try the syntax you mention
 
On further research the problem does not appear on Opening the db but on trying to create a link to the table inside the DB,


DoCmd.TransferDatabase acLink, "Microsoft Access", "database=" & strDBPathname & ";pwd=" & pstrPassword, acTable, strTableName, strTableName


The error mesage is "3043Disk or Network Error"

I will try the syntax you mention

If you can't get this to work you could use TableDefs

Code:
Dim tbl As DAO.TableDef
 
Set tbl = CurrentDb.CreateTableDef("LocalTableName")
tbl.connect = "Ms Access;DATABASE=C:\MyDB.mdb ;pwd=password"
tbl.SourceTableName = "RemoteTableName"
CurrentDb.TableDefs.Append tbl
 
First of all TableDefs works fine.

I believe that you cannot use "DoCmd.TransferDatabase acLink" to create a link in database-protected db. The pwd parameter refers to the User Password, not the db Password.

I believe this is worth posting as a new thread. I will do just that.

Thanks again for your help.
 
DJkarl,

One more thing.

In addition to creating the link with acLink, I copy a table into the db which I create. I use the following for a non-password protected db.

DoCmd.TransferDatabase acExport, "Microsoft Access", strDBPathname, acTable, "ztblCurrent", strTableName, False

This does not work if the db is password protected. Any ideas on how I can copy the table without entering the db password?
 
I spent some time on this, but I now have the answer.

You can use Docmd.TransferDatabase both for exporting a table (acExport) to a password protected db and for setting a link (acLink).

You first have to open the database with the database password, then you can use acExport and acLink. If you want the code I can post.
 

Users who are viewing this thread

Back
Top Bottom