Digital Workshop

Hi

With Sandy's and other's help, I am trying to learn database skills.

Robin Garrett's tutorial:

http://www.digitalgrapevine.info/viewtopic.php?t=296&highlight=database

has been a help. Using it, I now have a very basic sample.

I'm stuck trying to set up a password for the query to the Access database. Having set a simple password: open, I have then gone into the database tab in properties, added the "open" password to the editor area. It won't work and allow the query. It worked fine when the password is removed from the Access file. Adding a password and entering it in the database tab doesn't work.

The one thing I notice is that Access has no place for a user name to add with a password. The database tab in Opus has a user name above password, with a default to "admin." Whether I leave "admin" or change it to blank or enter "open" there too, it doesn't help: the query, which works fine without a password in Access, fails.

I'll post the sample .imp and 2 Access mdb files (one with, and one without the password) in a zip folder, if anyone wants to take a look, see if they can use the password protected Access file in Opus, get it working. Please let me know if anything else is needed.

Any help would be appreciated.

Kind Regards,

why I switched to mySQL.

Enter the username: admin and password: open into PUBLISHED section of the DATABASE dialogue box.

you don't need to promot for password.

Mackavi

Hi

Thank you for your suggestion.

The project requires encryption to safeguard health sensitive data. Password alone, without encryption, won't work. Excel has password but not encryption. Access 2007 has password plus encryption: Microsoft's new crypto standard (but, earlier Access like 2003 has password, but I don't believe it has encryption). Generally, 128 bit encryption minimum is required for health data electronically transmitted.

So, if mySQL has password 128 encryption, I could try to make it work. However, I plan to include the database in the setup/executable, and not connect to it online. Can mySQL be included as a standalone database?

Does anyone know if mySQL can be encrypted within the program settings, and not require third-party add-on's? Where would I get a copy of mySQL? (Perhaps, it's already on my computer and I don't know it? Part of MS Office 2007?).

Again, thank you for your suggestions.

Kind Regards,

OK--I think I see what you are trying to do.
I downloaded your sample, I had to create a new DSN to map it on my system, then attach the NAME/AGE/OCCUPATION to Field1/Field2/Field3 and it works fine. I can click the next record/previous record buttons and it steps through the names!! works well!!!
You don't have a SQL query yet, so it steps through all records


You are using the password in access, which probably will prevent Opus from accessing the database?? ( I used the no password version above) You can set up a password in Opus, but for some reason, which I haven't figure out--the development password id not working??

Hi Sandy

Yes, the problem is with the password. I am using the Access 2003 file format to test, so others can open it in Access 2003 if needed. Can't get the password that I've set in Access to be accepted upon query from Opus, even though I've set the corresponding password in Opus correctly on the database tab>editor section. Can't figure out what to enter for user name, since Access doesn't display one.

I need to solve the password first between Opus and Access 2003. Then, I would need to move up to encryption password in Access 2007 which has the accdb file format and better security instead of mdb (by the way, I tested the accdb without password in Opus, and with the right driver selected, it also works fine, but again, not with a password added).

Any help about how to use database password in Opus to query a password protected database would be appreciated. Adding the matching password doesn't seem to do it.

Kind Regards,

Sorry Stephen - confused now,

I used the open password in your Access file and set the options as above in Opus and everything connected fine. Can't say for 2007.

mySQL is at http://www.mysql.com/

although it's not portable like mbd files. Yes encryption is built in to it.

What we need is an encrypt function in Opus.

Hi

Unfortunately, in my haste to post, I think I have not changed the Opus query setup's to reflect the correct test mdb files. It's possible you are opening the non-password protected mdb instead of the passworded one.

The Database31.mdb in the zip folder has password protection (password=open). In order for you to test it, I think it would need a DSN connection set up on your computer. If you can set that up and link it to Database31.mdb, please see if it will open first with no matching password added in Opus, then with the password added.

If no password is required or set in the Opus database tab>editor area, and it opens: uh oh! Can't figure this out. It shouldn't.

However, the more likely event will be that it won't open, even if you then add the password in Opus. If it opens, I must be setting up my Opus-side password settings incorrectly. Any ideas then will help me get it fixed.

I have re-loaded the zip folder, making sure it contains the Database31.mdb file with password protection. Please download again to assure you have the correct files.

I appreciate your help.

Need to get the password function set up both in Access and in Opus for query/entering new data.

Kind Regards,

Did all that already Stephen. But just to make sure, deleted everything and double checked step by step.

Opus is OPENING the password protected file using the username: admin and password open WITHOUT any problem.

Steps I took:

1.Download your zip file.
2.Extract to my desktop
3. Open MDB to check that it's passworded - it is!
4. Setup ODBC connection - NO USERNAME OR PASSWORD use file location
5. Open ILM file.
6. Edit actions of page 1 to create new ODBC link
7. Assign fields
8. Edit password settings in publication DB properties. See Attached.

Hi makavi

Thanks for your help. I think I've got it:

when I create a new DSN for the password protected mdb (Database 31.mdb) the wizard gets to a request for user name and password (I think it needed to be set twice). In each case I added admin and open, then the same for the database dialog box in Opus. And, it seemed to work.

I then set a password with encryption in Access 2007 using the accdb file format (better security). Repeated the steps, and it worked fine again.

Fantastic! Now, I have some more learning to do, to figure out how to write data entered in text input boxes in Opus, using variables, into the Access database table.

Kind Regards,

OK--I've tried both the password version and non password.
I set the opus password to match the access one and it just will not open??.
use the 'no password' and it works fine.
I put a Debug.trace just before the database query, so it's getting there, just not opening the database.


I am fairly convinced that I've set everything up OK, I deleted the password and went to edit the database query and was prompted for the password and asked if I wanted to set this in Opus(accepted) then went back to Pub properties and the password had been entered, but....for some reason it's just not allowing the table to open??

I also deleted the DSN, the query and recreated them...tried using exclusive access to the database...still not open??

Hi

While the accdb file may not be editable unless you have Access 2007, the mdb (Database31.mdb) with password is (password=open).

So, I'll attach the latest .imp plus Database31.mdb in a new zip folder (sample3.zip).

My copies work, once I deleted the entire query database action and started over, inserting a new query action under the trigger. I also set up a new connection, which did ask twice for user name and password in 2 dialog boxes. I used admin and open, then also added these in 2 places in the database dialog box: editor area and publish area (for good measure).

When I test, it works. Same for password encrypted Access 2007 accdb version of the same database (set up separate new connection, since the ODCB driver is different to accommodate both mdb and accdb).

Let me know how it works for you.

Kind Regards,

Hi Stephen,

Yes the 2007 version works fine!! However, you are going to need to be able to create an OBDC connection on your host machines. Anybody without Office 2007 is going to need the accdb driver files (like me)

http://www.microsoft.com/downloads/deta ... laylang=en

Mackavi

P.S there is no .mdb file in the zip

It still won't work for me--I created a new database specifically to test the problem and it still will not open a password protected database, but at least you have it going which was the original problem!!

Hi Mackavi

I'm afraid that I missed that: putting the mdb in the sample 3 zip.

I'll do that now, so the folder will have the password protected mdb (Database31.mdb) should anyone wish to test it.

I suspect, Sandy, that if you are trying to test the accdb file instead of the mdb file, without the accdb driver, it may be why its not working. Sorry.

Mackavi: if all I want to do is include the database with its table1 in the Opus pub (or sys pub dir via setup installer), will the end-user need the accdc 2007 driver to use it within the pub? If so, that would be a bummer!
A major, make or break constraint on using a database to store data, is ability to encrypt at 128 bits. Access 2007 with its accdb is OK, but if I have to use Access 2003 with its weaker security, that's a "no go."

Please let me know if the end user's having the actual program or latest database driver is required for use in runtime of the published version. I was under the impression that as long as I included the database, they would not need Access or the driver to use it within Opus. I can understand the separate need for Access and the driver for our testing purposes.

Kind Regards,

Stephen,

Always assumed it would. I don't think that it's one of those things thats gets integrated into the publication file - I 'm going to publish your ILM file and remove the ODBC settings and test this theory. Will get back to you after some tea and testing.

Anyone else: have attached a full screen flash exe of the testing, and working of attaching to stephen's encrypted DB for help. This is only a rough guide, but shows it working - hope it helps.


Mack