Password broke the query! 1

[billybobk]

Some of you might recall this query from a previous thread of mine:
-----------------------------------
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\common\;" & _
"Extended Properties=dBASE IV;" & _
"User ID=Admin;Password="

strSQL = "SELECT Username, name FROM Users WHERE Username NOT IN (SELECT GMLoginname FROM myTable IN 'c:\myPath\Lat.mdb')"

etc....
-----------------------------------
Well we added a password to the Lat.mdb. That broke it. The connectionstring is for the 'users.dbf' table, but how do I set a password for the other, mdb table? There was no connectionstring for it, the IN took care of that! Now with the password protection, that cool query won't work! Any clues? As usual, thanks in advance!

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[CCLINT]

Try:
IN 'c:\myPath\Lat.mdb' [;pwd=mypassword]

or

IN '' [;DATABASE=c:\myPath\Lat.mdb;pwd=mypassword]

 

[billybobk]

cclint: I marked this a helpful post, another star for you. But really, how on earth did you know the answer? Your programming knowledge is incredible!

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[billybobk]

Sorry, I spoke too soon! Can't get those to work. As soon as I delete the password from the database and from the query, BAM! it works again. If you're still out there, we can resume the thread! Thanks,

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[CCLINT]

OK.
Works fine here though.

So, post the new sql statement you have made (using both syntax given), and post what error do you receive?
Try using this with a connection on a MDB instead of the dBase (create a temp mdb and import the Users table, or a sample of it, into the mdb, and then use this instead of the dBase table just to see if this is alright).

 

[billybobk]

Hi again:
OK this works:
-----------------------------
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDirectory & ";" & _
"Extended Properties=dBASE IV;" & _
"User ID=Admin;Password="

strSQL = "SELECT Username, Name FROM Users " & _
"WHERE Username NOT IN (SELECT GMLoginname FROM Pegboard IN '" & App.Path & "\Lat.mdb')"
------------------------------
This does NOT: (Error is "Not a valid password", but it IS valid)
--------------------------------
strSQL = "SELECT Username, Name FROM Users " & _
"WHERE Username NOT IN (SELECT GMLoginname FROM Pegboard IN '" & App.Path & "\Lat.mdb'[;pwd=bill])"
-------------------------------
Thanks!

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[CCLINT]

strSQL = "SELECT [Username], [Name] FROM [Users] " & _
"WHERE [Username] NOT IN (SELECT [GMLoginname] FROM [Pegboard] IN '' [;DATABASE=" & App.Path & "\myPath\Lat.mdb;pwd=bill])"

or

strSQL = "SELECT [Username], [Name] FROM [Users] " & _
"WHERE [Username] NOT IN (SELECT [GMLoginname] FROM [Pegboard] IN '' [;DATABASE=" & App.Path & "\myPath\Lat.mdb;User=Admin;pwd=bill])"

 

[billybobk]

Oh, I should add that I tried your other way also, and it didn't return an error, It simply returned nothing at all. And another way from another colleague (also using brackets) returned nothing. Maybe it's me, i dunno.

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[CCLINT]

Did the recordset open with no problem, just no data?

 

[billybobk]

Yes. Just no data, except for the first way above with the password error.

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[CCLINT]

Then the problem doesn't appear to be with the password or connection.

Please try this as mentioned using a temp MDB as the main mdb. Then we will see tomorrow. I still have something else in mind....

 

[billybobk]

Now wait! Are you saying to literally use brackets around the values as in your example above? What is that called?

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[CCLINT]

billybobk,

Does just using this produce results?

strSQL = "SELECT [GMLoginname] FROM [Pegboard] IN '' [;DATABASE=" & App.Path & "\myPath\Lat.mdb;User=Admin;pwd=bill])"

 

[engineer2100]

Hi,

I simply love this forum. I really am learning new tekniks here.

Regards
Engi

 

[billybobk]

cclint:
-----------------
strSQL = "SELECT [Username], [Name] FROM [Users] " & _
"WHERE [Username] NOT IN (SELECT [GMLoginname] FROM [Pegboard] IN '' [;DATABASE=" & App.Path & "\myPath\Lat.mdb;User=Admin;pwd=bill])"
------------------
That did it!
Thanks so much for all the help. What do you call the bracket thing?
p.s I'll try your other ideas too, just for curiosity's sake! & let you know shortly.

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[CCLINT]

>What do you call the bracket thing

You mean around the fields or the database info?
Around the fields it tells the dbms to not confuse these with any ANSI, or a specific dbms, reserved word but to parse them out differently and use them as field, table, etc. names. This prevents things like a reserved word USER, which returns the db logged on user name, from being confused with a field name called User.
The same with the database info, but it also tells JET that how it needs to parse the connection information differently as with the information with-in the single quotes.

The actual definition name? I guess name/list identifiers or something like that.

 

[billybobk]

The last one: it doesn't seem to work, but this from another expert I know, it does:
----------------------
strSQL = "SELECT Username, name FROM Users " & _
"WHERE Username NOT IN (SELECT GMLoginname FROM Pegboard " & _
"IN '' [;DATABASE=" & App.Path & "Lat.mdb;pwd=bill])"
-----------------------
And an 'Aha!' to the last piece of info. How long have you been a programmer? Hundred years or so?

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[CCLINT]

I'm not understanding you. Are you saying the this very last sql posted by you works? Or not? Looks the same as one previously posted.

 

[billybobk]

cclint, It's just that one has the brackets around the field names, the other doesn't... and then, since I've got your attention, right after the IN clause the two single quotes just seem to be hanging there. The query doesn't work without them, however, cuz I tried it. Another MYSTERY for me, unless, maybe...just maybe....?
(oh, and there should be a \ before Lat in the last query.)

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[billybobk]

(they both work!)

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran