AXS2000: Create ADO connection to external database

  • Thread starter Thread starter Wolfgang Kreuzer
  • Start date Start date
W

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
 
Hi,
thanks for your quick response.
How do I change the Database-Name?
Best regards
Wolfgang
 
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?
 
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?
 
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
 
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
 
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
 
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
 
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
 
Back
Top