AXS2000: Create ADO connection to external database

[Wolfgang Kreuzer]

I need to create an ADO connetcion by code to pass it over to an ADOX
library as parameter.
The conection to the database I want to pass over is an (secured)
external database (MDB) and I want to use the login of the current
session and not ask the user to enter the login information again.

In DAO I would use the workgroup(0) and oper the database in that
context. Up til now I found no way to do similar in ADO.

Any help is appreciated.

Regards

Wolfgang

 

[Alex Dybenko]

Hi,
try:
CurrentProject.Connection

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Wolfgang Kreuzer]

Hi,
thanks for your quick response.
How do I change the Database-Name?
Best regards
Wolfgang

 

[Alex Dybenko]

You can use replace() function and replace currentdb.name with other one

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Wolfgang Kreuzer]

Alex,

thanks - I tried two versions and got different errors

Public Sub test1()
Dim cnn As ADODB.Connection
Dim strTest As String

Set cnn = CurrentProject.Connection

strTest = cnn.ConnectionString
Debug.Print strTest

strTest = ChangeParamToken(strTest, "Data Source", "S:\WrkGrpDB\16Bit
\OPENPNTS_V140.MDA", ";", "=")
Debug.Print strTest

cnn.ConnectionString = strTest
cnn.Open

Set cnn = Nothing

End Sub

Public Sub test2()
Dim cnn As ADODB.Connection
Dim strTest As String

Set cnn = New ADODB.Connection

strTest = CurrentProject.Connection.ConnectionString
Debug.Print strTest

strTest = ChangeParamToken(strTest, "Data Source", "S:\WrkGrpDB\16Bit
\OPENPNTS_V140.MDA", ";", "=")
Debug.Print strTest

cnn.ConnectionString = strTest
cnn.Open

Set cnn = Nothing

End Sub

Test1 terminates with error 3705 (illegal operation for open object;
translated from german by me) in line »cnn.ConnectionString = strTest«
Test2 (I didn't really believe that this would work) terminates with
error 0x80040e4d (invalid account or password)
The cause of Test2 is quite clear to me - I try to open a connection
to a secured database without specifying account and password. That is
exactly what I tried to work-around by using the current connection.

How can I change the connection string or the data source?

 

[Alex Dybenko]

Hi,
try:

strTest = Replace(strTest, currentdb.name,
"S:\WrkGrpDB\16Bit\OPENPNTS_V140.MDA")


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Alex,

thanks - I tried two versions and got different errors

Public Sub test1()
Dim cnn As ADODB.Connection
Dim strTest As String

Set cnn = CurrentProject.Connection

strTest = cnn.ConnectionString
Debug.Print strTest

strTest = ChangeParamToken(strTest, "Data Source", "S:\WrkGrpDB\16Bit
\OPENPNTS_V140.MDA", ";", "=")
Debug.Print strTest

cnn.ConnectionString = strTest
cnn.Open

Set cnn = Nothing

End Sub

Public Sub test2()
Dim cnn As ADODB.Connection
Dim strTest As String

Set cnn = New ADODB.Connection

strTest = CurrentProject.Connection.ConnectionString
Debug.Print strTest

strTest = ChangeParamToken(strTest, "Data Source", "S:\WrkGrpDB\16Bit
\OPENPNTS_V140.MDA", ";", "=")
Debug.Print strTest

cnn.ConnectionString = strTest
cnn.Open

Set cnn = Nothing

End Sub

Test1 terminates with error 3705 (illegal operation for open object;
translated from german by me) in line »cnn.ConnectionString = strTest«
Test2 (I didn't really believe that this would work) terminates with
error 0x80040e4d (invalid account or password)
The cause of Test2 is quite clear to me - I try to open a connection
to a secured database without specifying account and password. That is
exactly what I tried to work-around by using the current connection.

How can I change the connection string or the data source?

 

[Wolfgang Kreuzer]

Hi,

I assume you you refer to Sub Test1 where the replace function should
be placed.
My own function ChangeParamToken does exactly the same but I tried it
with replace as well.

Public Sub test1a()
Dim cnn As ADODB.Connection
Dim strTest As String

Set cnn = CurrentProject.Connection

strTest = cnn.ConnectionString
Debug.Print strTest

'strTest = ChangeParamToken(strTest, "Data Source", "S:\WrkGrpDB\16Bit
\OPENPNTS_V140.MDA", ";", "=")
strTest = Replace(strTest, CurrentDb.Name, "S:\WrkGrpDB\16Bit
\OPENPNTS_V140.MDA")
Debug.Print strTest

cnn.ConnectionString = strTest ' <--- Error 3705
cnn.Open

Stop

Set cnn = Nothing

End Sub

The result is identical to my first attempt, I get error 3705
(Operation is not allowed when the object is open). Do you have any
suggestion how to bypass this?

Regards

Wolfgang

 

[Alex Dybenko]

Ahh, ok
you need to create a new connection:

Dim cnn2 As new ADODB.Connection

....

cnn2.ConnectionString = strTest
cnn2.Open


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi,

I assume you you refer to Sub Test1 where the replace function should
be placed.
My own function ChangeParamToken does exactly the same but I tried it
with replace as well.

Public Sub test1a()
Dim cnn As ADODB.Connection
Dim strTest As String

Set cnn = CurrentProject.Connection

strTest = cnn.ConnectionString
Debug.Print strTest

'strTest = ChangeParamToken(strTest, "Data Source", "S:\WrkGrpDB\16Bit
\OPENPNTS_V140.MDA", ";", "=")
strTest = Replace(strTest, CurrentDb.Name, "S:\WrkGrpDB\16Bit
\OPENPNTS_V140.MDA")
Debug.Print strTest

cnn.ConnectionString = strTest ' <--- Error 3705
cnn.Open

Stop

Set cnn = Nothing

End Sub

The result is identical to my first attempt, I get error 3705
(Operation is not allowed when the object is open). Do you have any
suggestion how to bypass this?

Regards

Wolfgang

 

[Wolfgang Kreuzer]

Hi,

this is similar to my test2 procedure. I modified it according to your
suggestion and receive the same error as before:

Public Sub test2a()
Dim cnnCur As ADODB.Connection, cnnNew As ADODB.Connection
Dim strTest As String

Set cnnCur = CurrentProject.Connection
Set cnnNew = New ADODB.Connection

strTest = cnnCur.ConnectionString
Debug.Print strTest

strTest = Replace(strTest, CurrentDb.Name, "S:\WrkGrpDB\16Bit
\OPENPNTS_V140.MDA")
Debug.Print strTest

cnnNew.ConnectionString = strTest
cnnNew.Open ' <--- error 0x8004 0e4d
(Autentification failed)

Stop

Set cnnCur = Nothing
Set cnnNew = Nothing

End Sub

This modified procedure terminates with identical error -
Autentification failes (as Test2).
I assume it is caused by the missing userid and password as the
connection string doesn't contain that information.

This is the point where started from: How can I get the password for
the current user without asking him/her to enter it again after the
login screen presented by access?

Regards

Wolfgang

 

[Alex Dybenko]

Hi,
I think I understand now what you want to achieve. Do you need to make data
structure modification? if yes - then I suggest to use DAO, currently I
don't know how to make such connection in ADO. But would be good to know if
you find it


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi,

this is similar to my test2 procedure. I modified it according to your
suggestion and receive the same error as before:

Public Sub test2a()
Dim cnnCur As ADODB.Connection, cnnNew As ADODB.Connection
Dim strTest As String

Set cnnCur = CurrentProject.Connection
Set cnnNew = New ADODB.Connection

strTest = cnnCur.ConnectionString
Debug.Print strTest

strTest = Replace(strTest, CurrentDb.Name, "S:\WrkGrpDB\16Bit
\OPENPNTS_V140.MDA")
Debug.Print strTest

cnnNew.ConnectionString = strTest
cnnNew.Open ' <--- error 0x8004 0e4d
(Autentification failed)

Stop

Set cnnCur = Nothing
Set cnnNew = Nothing

End Sub

This modified procedure terminates with identical error -
Autentification failes (as Test2).
I assume it is caused by the missing userid and password as the
connection string doesn't contain that information.

This is the point where started from: How can I get the password for
the current user without asking him/her to enter it again after the
login screen presented by access?

Regards

Wolfgang

 

[Wolfgang Kreuzer]

Hello all,

got help meanwhile on german forum.

A solution to the problem to retrieve the password of the current user
ist the undocumented WizHook-object.
I hate use of undocumented as the may disapear in future versions a
the problem pops up again, but here it seems that MS left the users
alone.

Another draw-back (for me) is that the function works for Axs02/XP (no
idea if Axs07 is ok) only.

Code sniplet

Dim fResult As Boolean
Dim strUserID As String
Dim strPassWd As String

WizHook.Key = 51488399

fResult = WizHook.AdpUIDPwd(strUserID, strPassWd)

After successful execution of AdpUIDPwd you have everything you need
to know to open a connection using the standard way.

Again - I definitely do not like this solution, but it works for the
moment.
If somebody out there knows a documented solution for my problem, I
would love to know it.

Regards

Wolfgang