Access password problem in VB
I had wanted to password protect a Access database so that no other person would've a look at my data structure.But I encountered a peculiar problem.
I'd successfully transfered data,in my VB program,from one Access database to another using a Insert...Select query in which I'd stated the full path to the source database.But I'd also stated a password for the source database(which was also mentioned in the connection string).And because of this password I got an error when the query was being executed.After removing the password the query got executed.
As a workaround I assigned a password to the default user,"admin".For this I'd to state the User-id & password in the connection string & the query execution was ok.But for this I've to paste the System.mdw file in the relevant(Office) folder of the users' computer.And this may mean overwriting an existing System.mdw containg user accounts - which would be a nasty thing to do.But without it my database will be left unprotected.
And the aforestated Insert...Select query is indeed a powerful tool that I'd not want to dispense with.Can you help?
[1162 byte] By [
softraj] at [2007-11-11 8:49:02]
# 1 Re: Access password problem in VB
Could you post an example of the INSERT...SELECT syntax for your query? In addition, you may want to identify whether you are using just a single database password or complete user-level security.
# 2 Re: Access password problem in VB
Query syntax : INSERT INTO <table> SELECT.... IN "<full path to the .mdb file>".
I'd used a "single database password" which resulted in the error.
# 3 Re: Access password problem in VB
If you password protect your database (or in this case both d/b's), then open it/them with the password/s in the connect string, then your prog does not need to subsequently use passwords for any d/b access (insert, update, select etc) if you read from 1 d/b into a recordset, then loop through inserting into the other. Bear in mind that there are ways of 'discovering' Access passwords ;-)
gupex at 2007-11-11 17:26:59 >
# 4 Re: Access password problem in VB
It is possible to use your own System.mdw file. You can name it whatever you want. You just need to make certain to specify the location in the connection string. You can find an example at the below link (Workgroup - System Database)
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMicrosoftJet
# 5 Re: Access password problem in VB
pclement,I do not need to use the System.mdw file.Because,I am not interested in creating multi-user security profile.I only want to set the db password - but which results in an error when the INSERT INTO <table> SELECT... query is executed.
gupex,I am not interested in using the recordset.If I've as powerful an alternative as the aforestated query to transfer data from one db to another,why use the recordset which will surely be much slower.
# 6 Re: Access password problem in VB
So then set your database password and try something like the following:
INSERT INTO [AccessTable] SELECT * FROM [MS Access;DATABASE=e:\My Documents\db12.mdb;PWD=password].[Table1]
# 7 Re: Access password problem in VB
pclement,your solution worked! :)
It is a gem of a solution I never knew existed.It almost looks weird!
Thanks a lot for solving my problem because of which I was totally vexed.
I wonder whether even the MSDN has this solution.With such a powerful action query who needs the recordset to transfer data from one db to another?
Besides,the 2nd pair of square brackets around the table name can be done away with.
