By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

Problem with password in Connect parameter of OpenDatabase method

100+
P: 116
Can anyone tell me just how to format the Connect parameter in an OpenDatabase call?

I want to provide an option in my front end to backup the back end. To do so I need to open the back end exclusively. I am using the following code, after extraxting the connect string from one of the linked tables:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print strConnect    ''''''''''''''''''''''''''''''''''''''''''''  TEMP
  2. '   Extract the password
  3. PwStart = InStr(strConnect, "PWD=")                ' Start of the password string
  4. PwEnd = InStr(PwStart, strConnect, ";")            ' End of the password string
  5. PwString = Mid(strConnect, PwStart, PwEnd - PwStart)
  6.  
  7. Debug.Print strBackEnd, PwString   '''''''''''''''''''''''''''''''''''''''''''''''  TEMP
  8. On Error Resume Next
  9.    ' Attempt to open backend exclusively
  10. Set dbs = DBEngine.OpenDatabase(Name:=strBackEnd, Options:=True, Connect:=PwString)
  11. 'Set dbs = DBEngine.OpenDatabase("'" + strBackEnd + "'", True, False, PwString)
  12.  
  13. Select Case Err.Number
  14. '   etc
Using the first Set statement above I get Error 3031, "Not a valid password". I found NeoPa's helpful post of Feb 10 '13 on that error, so I added "ReadOnly:=False, " before the Connect argument. This then caused it to give Error 3151, "ODBC - connection to '[pathname]' failed." The second form of the Set statement did the same, with or without the quote marks around the strBackEnd (which contains spaces).

The two Debug.Print statements give the following (after changing the passwords):
Expand|Select|Wrap|Line Numbers
  1. MS Access;PWD=XYZ;DATABASE=K:\emmaus bs db testdata_be.accdb
  2. K:\Emmaus BS DB livedata_BE.accdb         PWD=XYZ
, the passwords being identical in both.

I also tried changing the code which extracts the passowrd from the connect string, to extract just the password and not the "PWD+", but that didn't help.
I added the following code to the error processor:
Expand|Select|Wrap|Line Numbers
  1.      Dim er As Error
  2.     Debug.Print Time$, "Dumping " & DBEngine.Errors.Count & " error records:"
  3.     For Each er In DBEngine.Errors
  4.         Debug.Print er.Number, er.Description, er.Source
  5.     Next er
, but it didn't add any helpful information:
Expand|Select|Wrap|Line Numbers
  1. 08:16:51      Dumping 1 error records:
  2.  3031         Not a valid password.       DAO.Workspace
or
Expand|Select|Wrap|Line Numbers
  1. 08:28:13      Dumping 1 error records:
  2.  3151         ODBC--connection to 'K:\Emmaus BS DB livedata_BE.accdb' failed.       DAO.Workspace
Where do I turn next? Any helpful ideas?
2 Weeks Ago #1

✓ answered by NeoPa

That's really not my understanding at all Petrol. before I go further though, I'll run some tests. I do not expect an error message which starts "ODBC" anything, just so you understand where I'm coming from.

I ran the test and got the same results as you did. It seems that when using ACE there is a default parameter of "MS Access" that can be left unspecified if not present - but if omitted then the separating semi-colon (;) is required. So, if you want to use the minimum text possible/necessary then ";PWD=XYZ" certainly seems to work (I'd worked on the basis that at least those three were required for a type of "MS Access"). If this isn't provided you get an ODBC error as you haven't specified that it be handled by Jet / ACE.

Petrol:
Unfortunately all the documentation of Opendatabase I could find on the web just said that Connect "specifies various connection information, including passwords."
This is because the Connect parameter has different requirements based on the Type of database used.

Going back to the Connect parameter you used and got to work, the semi-colon indicates that the Type - the first parameter which doesn't even have a name (Not "Type=MS Access" notice, but just "MS Access") - which determines which other parameters are expected, and even processed, has not been specified at all. To illustrate I will show the (password & client obscured) data found in the .Connect property of two tables I have which are both linked, but one is Jet (MS Access) & the other is SQL Server (ODBC).
Expand|Select|Wrap|Line Numbers
  1. MS Access;PWD=????????;DATABASE=C:\Branches\Developer\Developer Data.Mdb
  2. ODBC;Description=Data for Front Office;DRIVER=SQL Server Native Client 11.0;SERVER=XXXX20\SQLEXPRESS;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;WSID=XXXX05;Network=DBMSSOCN;
You can see from that data that the number of named parameters to a Connect string is neither known nor even fully under the control of Microsoft. A new provider can come out any time and existing providers can specify extra named parameters as and when they choose to.

The "MS Access" parameters may be relatively straightforward, but they are by no means the whole story. I imagine that, to find info on each possibly type you'd need to search using the type name in your search. In my case I found out by examining existing Connect parameters and working from there.

Getting back to your original idea that the missing semi-colon explains your problems, I don't see any evidence to support that. Your OP indicated you were using the Connect string that I tested and found to work perfectly. However, your later comments indicate otherwise. If you were using anything without either the "MS Access;" or just the semi-colon on its own at the start, then that would explain your problem. If you were using the full string then I can only imagine you had the name wrong in some detail. That or there is some fundamental difference between what you're working with and either the 2010 or 2003 systems I tested it on.

Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,606
Where have you hidden your actual problem in all this?

That probably sounds more harsh than you deserve. Clearly you've gone to a lot of trouble to provide as much information as possible. Unfortunately though, discrimination is also necessary when posting questions. I suspect this is a relatively straightforward question - but there are so many trees in the way I can't see the wood.

I don't need to see the code you use to create a Connection string with if I'm checking if it's in the right format.
2 Weeks Ago #2

100+
P: 116
Sorry. I've seen plenty of posts which have been replied to by "Give us more information", so I guess I tried to give all the necessary clues at the start.

So let me start again:
I am trying to open a database exclusive, using the statement
Expand|Select|Wrap|Line Numbers
  1. Set dbs = DBEngine.OpenDatabase(Name:=strBackEnd, Options:=True, ReadOnly:=False, Connect:=PwString)
  2.  
where strBackEnd = K:\Emmaus BS DB livedata_BE.accdb.

The result is Error 3151, "ODBC--connection to 'K:\Emmaus BS DB livedata_BE.accdb' failed."

I have also tried it with strBackEnd = 'K:\Emmaus BS DB livedata_BE.accdb' (i.e. quoted), with the same result, except that there are now of course two quote marks around the pathname in the error message..
2 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,606
Petrol:
Sorry. I've seen plenty of posts which have been replied to by "Give us more information", so I guess I tried to give all the necessary clues at the start.
That makes perfect sense, and I do sympathise. This, on the other hand, is so much less work to interpret. Nice job.

One missing item is the exact text in PwString but I'll get that from the OP (MS Access;PWD=XYZ;DATABASE=K:\emmaus bs db testdata_be.accdb). That appears to be the format that I use so that seems fine. I never allow spaces in names for my databases so I'll do some further testing to see if I run into any problems. For my test I grabbed a database I had lying around, renamed it to match yours and gave it a password of exactly "XYZ".

I'm sorry to report that the code worked first time for me. No issues with spaces in the name. It just worked.

I would see this as indicating you have some other issue, either with your system or this database specifically.
2 Weeks Ago #4

NeoPa
Expert Mod 15k+
P: 31,606
I don't know if this helps, but the fact that your error message starts with "ODBC--connection to " is a bit weird. I see nothing in the code call you've included that would cause it to involve ODBC in any way.

Access only - IE DAO - gives completely different error messages. I tried the same code that was working, but with an extra character in the name so it wasn't correct, and the error I saw was :
Microsoft Visual Basic for Applications:
Run-time error '3024':

Could not find file 'D:\Home\Adrian.Bell\Access\Emmaus BBS DB livedata_BE.AccDB'.
You'll see the extra character added was to say BBS instead of BS.

I hope that points you towards something.
2 Weeks Ago #5

100+
P: 116
Well it seems that my problem was well expressed in the first sentence of the OP: I just didn't know how to properly format the Connect argument in an OpenDatabase call. Unfortunately all the documentation of Opendatabase I could find on the web just said that Connect "specifies various connection information, including passwords."

After many hours of testing various possibilities and different databases it turns out that "Connect:=;PWD=XYZ" is the way to go. If you leave out the semicolon, as my code was doing, you get the rather unhelpful error 3151, "ODBC--connection failed"

(I'm sure you were aware of the need for the semicolon, NeoPa, since your test worked; but you probably didn't realise just how ignorant I was!) Anyway, now I can open exclusive - the next step is to do the backup. Tomorrow!
2 Weeks Ago #6

NeoPa
Expert Mod 15k+
P: 31,606
That's really not my understanding at all Petrol. before I go further though, I'll run some tests. I do not expect an error message which starts "ODBC" anything, just so you understand where I'm coming from.

I ran the test and got the same results as you did. It seems that when using ACE there is a default parameter of "MS Access" that can be left unspecified if not present - but if omitted then the separating semi-colon (;) is required. So, if you want to use the minimum text possible/necessary then ";PWD=XYZ" certainly seems to work (I'd worked on the basis that at least those three were required for a type of "MS Access"). If this isn't provided you get an ODBC error as you haven't specified that it be handled by Jet / ACE.

Petrol:
Unfortunately all the documentation of Opendatabase I could find on the web just said that Connect "specifies various connection information, including passwords."
This is because the Connect parameter has different requirements based on the Type of database used.

Going back to the Connect parameter you used and got to work, the semi-colon indicates that the Type - the first parameter which doesn't even have a name (Not "Type=MS Access" notice, but just "MS Access") - which determines which other parameters are expected, and even processed, has not been specified at all. To illustrate I will show the (password & client obscured) data found in the .Connect property of two tables I have which are both linked, but one is Jet (MS Access) & the other is SQL Server (ODBC).
Expand|Select|Wrap|Line Numbers
  1. MS Access;PWD=????????;DATABASE=C:\Branches\Developer\Developer Data.Mdb
  2. ODBC;Description=Data for Front Office;DRIVER=SQL Server Native Client 11.0;SERVER=XXXX20\SQLEXPRESS;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;WSID=XXXX05;Network=DBMSSOCN;
You can see from that data that the number of named parameters to a Connect string is neither known nor even fully under the control of Microsoft. A new provider can come out any time and existing providers can specify extra named parameters as and when they choose to.

The "MS Access" parameters may be relatively straightforward, but they are by no means the whole story. I imagine that, to find info on each possibly type you'd need to search using the type name in your search. In my case I found out by examining existing Connect parameters and working from there.

Getting back to your original idea that the missing semi-colon explains your problems, I don't see any evidence to support that. Your OP indicated you were using the Connect string that I tested and found to work perfectly. However, your later comments indicate otherwise. If you were using anything without either the "MS Access;" or just the semi-colon on its own at the start, then that would explain your problem. If you were using the full string then I can only imagine you had the name wrong in some detail. That or there is some fundamental difference between what you're working with and either the 2010 or 2003 systems I tested it on.
1 Week Ago #7

100+
P: 116
Well that certainly clarifies it. I hadn't thought about a missing type argument. What I've got (pwString = "Connect:=;PWD=XYZ") certainly works now, but perhaps for clarity for possible future developers I'll change it to pwString = "Connect:=MS Access;PWD=XYZ".
Thanks for the clarification.

On your later points, the reason for my comment about the semicolon was the results of my testing. When I put the connect string as PWD=XYZ I got Error 3151; when I used ;PWD=XYZ it worked. (One of my other unsuccessful attempts was to use just XYZ, without the PWD=. This generated the equally unhelpful error 3170, "Could not find installable ISAM"!). I find the stray messages about ODBC and ISAM (neither of which I know much about) quite confusing, but I do appreciate the difficulty of coding specific messages in a procedure that may be several levels away from what the user (me, in this case) is entering!
Thanks again.
1 Week Ago #8

NeoPa
Expert Mod 15k+
P: 31,606
I really can't explain exactly why they came up with the format they did for the Connect string, but it sort of works on :
  1. First parameter is Type, but it's always that so no need to put "Type=" first.
    (I know. To whom did that make any sort of sense?)
  2. Once Type is determined all the other parameters must be introduced by the name of the parameter because they vary. They really vary. Quite a lot.

ODBC (Object Database Connectivity to give its fully qualified name.) is a very general term and there are at least hundreds (probably thousands) of such drivers for various different servers and even files.

An ISAM (or Indexed Sequential Access Method) is just a way of referring to databases that hold their data other than simply in sequence. The structure is not flat even though the file or files on the system may be so. IE. It extracts data based on indices.

The reason you saw a message starting with ODBC was that if you don't specify Jet/ACE either by "MS Access;" or the default ";" it has no idea what it is you want - or which driver even to pass the request on to. In this case it was the ODBC driver that drew the short straw and was left trying to give you a helpful message. A little unfair as it had no clue from your string what you even wanted. That's why it got so complicated.

Now think about a Connect string of just XYZ. What happens? :
  1. Check first parameter (XYZ)
  2. Woah. Not Access? No.
  3. Not any sort of ODBC? No.
  4. Any other type of ISAM we've ever heard of? No.
  5. Hmmm. Error 3170 - "Could not find installable ISAM"
    Translated into more common English :
    We haven't the first idea what you want from us. Help!!

I hope that gives you some understanding of why this can be a complicated area and why the response was, necessarily, so unspecific.
1 Week Ago #9

100+
P: 116
That makes it very clear and understandable. Thank you. Very helpful.
If the OpenDatabase documentation had included that information it would have saved us both a lot of time and trouble over the past 5 days! But thanks for all yours.
I thought I had marked your previous answer (#7) as the best answer, but I can't see any evidence of that now, and it no longer invites me to choose one. But both this and the previous answers will doubtless prove very helpful to me and others.
1 Week Ago #10

NeoPa
Expert Mod 15k+
P: 31,606
It still shows as set for me Petrol.

All good, & happy to help :-)
1 Week Ago #11

Post your reply

Sign in to post your reply or Sign up for a free account.