473,320 Members | 1,965 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem with password in Connect parameter of OpenDatabase method

204 128KB
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?
Jan 4 '20 #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.

10 2679
NeoPa
32,556 Expert Mod 16PB
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.
Jan 6 '20 #2
Petrol
204 128KB
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..
Jan 6 '20 #3
NeoPa
32,556 Expert Mod 16PB
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.
Jan 6 '20 #4
NeoPa
32,556 Expert Mod 16PB
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.
Jan 6 '20 #5
Petrol
204 128KB
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!
Jan 7 '20 #6
NeoPa
32,556 Expert Mod 16PB
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.
Jan 8 '20 #7
Petrol
204 128KB
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.
Jan 8 '20 #8
NeoPa
32,556 Expert Mod 16PB
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.
Jan 9 '20 #9
Petrol
204 128KB
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.
Jan 10 '20 #10
NeoPa
32,556 Expert Mod 16PB
It still shows as set for me Petrol.

All good, & happy to help :-)
Jan 10 '20 #11

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

Similar topics

3
by: Cindy Liu | last post by:
Hi Everyone, I created C# COM+ component. It has two overloaded methods - the method names are same and their signatures are different, one takes two parameters and another takes four. I coded...
0
by: os2 | last post by:
hi i have installed mysql via yast under suse 9 linux:/home/mcollin # rcmysql start Starting service MySQL done i try to change the password...
4
by: andy | last post by:
i am coding a dll which contains a function take a const wchar_t* as parameter, call it form a exe, the wchar_t is passed to dll function, but the function in dll will then call another function...
0
by: Stefan Fiedrich | last post by:
Hallo all, I have two dbs and I would like to open one db from another by calling a VBA function. The db I'll try to invoke has a password. What is my mistake ?? Option 1: Function Open1()...
1
by: dawn | last post by:
Hi, anyone can help me to solve this little problem that i've spent a lot of time to solve it!! but i can't!!!! actually, i use a DataGrid, and i'd like to save the changed data! ( this is...
4
by: sunniyeow | last post by:
Hi, My question is regarding password protecting 2 different folders inside a single virtual directory using forms authentication method. Easier if I illustrate things out... - <authentication...
1
by: Henry Stockbridge | last post by:
Hi, My computer just hiccuped, so if this went through already, my apologies. I am attempting to have a User access a report in a separate application based on the value from a combo box, but...
2
by: eboy98 | last post by:
if i have a dictionary name number ....and i want to ask the list whether a particular key already exists. {'octal': '1234567', 'binary': '10100101', 'decimal': '1234567890', 'hexadecimal':...
2
by: dounax | last post by:
Hello, I want to connect to an Oracle 9.2 database from a web site. - I have created a DSN file, I access the database from Excel - I have written the following vbScript, that works: ...
1
by: eishita | last post by:
Hi there I have created a server using Linux Mandriva 2010.0. but having problem while setting the database connectivity. Whenever I'm typing the command --- # mysqladmin -u root password...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.