Data from 2nd Database in a form

[Ludwin]

Hi,
I have a form with a listfield in witch I want to show
data from an other Database. It's an Oracle over ODBC and
I make Connections? (Verknuepfung in German) to the
tables I need. When I open that form I am prompted for
Login parameters for the ODBC. That all works fine.
What I want is that the user is prompted one time with a
login form. I will instantiate an new database object
with a connection to his user-account on Oracle and then
assign this database object to the underlying query in
the list field.

Can this easily be done?

If not: I found that I can state the connection in the
queries DestConnectStr
as "ODBC;DNS=xyz;UID=ABC;PWD=IDK;". That will work too.
But how can I assign the DestConnectStr at runtime (after
the user entered his Login Information)?

TIA
Cheers Ludwin

 

[Marshall Barton]

I have a form with a listfield in witch I want to show
data from an other Database. It's an Oracle over ODBC and
I make Connections? (Verknuepfung in German) to the
tables I need. When I open that form I am prompted for
Login parameters for the ODBC. That all works fine.
What I want is that the user is prompted one time with a
login form. I will instantiate an new database object
with a connection to his user-account on Oracle and then
assign this database object to the underlying query in
the list field.

Can this easily be done?

If not: I found that I can state the connection in the
queries DestConnectStr
as "ODBC;DNS=xyz;UID=ABC;PWD=IDK;". That will work too.
But how can I assign the DestConnectStr at runtime (after
the user entered his Login Information)?

This is a little over my head and I don't unserstand your
first approach. For your second idea, I think all you need
to do is construct the quey's SQL using the IN phrase in the
FROM clause:

strFROM = "FROM table IN '" & DestConnectStr & "'"

Again I'm not sure, but you probably want to add
;DATABASE=path
in there too.

Or maybe it would be better to use one of the alternate ways
to specify an external connection:

strFROM = "FROM table IN '" & DestConnectStr & "' '" _
& path & "'"
or you might prefer this kind of syntax:
strFROM = "FROM [" & DestConnectStr & "].table "

I find this kind of stuff to be rather confusing, so be sure
to double check Help for all the options you can use in the
FROM clause.

 

[Guest]

Hi Marsh,

thank you for your help. I also tryed the "In" approach.
But this application is installed to many users with
differnt DNS on there machines. The database parameters
are passed by an ini file. So I have to add the "IN"
stuff at runtime to the query. Do you know how I can do
that with VBA?

TIA
Ludwin

-----Original Message-----

I have a form with a listfield in witch I want to show
data from an other Database. It's an Oracle over ODBC and
I make Connections? (Verknuepfung in German) to the
tables I need. When I open that form I am prompted for
Login parameters for the ODBC. That all works fine.
What I want is that the user is prompted one time with a
login form. I will instantiate an new database object
with a connection to his user-account on Oracle and then
assign this database object to the underlying query in
the list field.

Can this easily be done?

If not: I found that I can state the connection in the
queries DestConnectStr
as "ODBC;DNS=xyz;UID=ABC;PWD=IDK;". That will work too.
But how can I assign the DestConnectStr at runtime (after
the user entered his Login Information)?

This is a little over my head and I don't unserstand your
first approach. For your second idea, I think all you need
to do is construct the quey's SQL using the IN phrase in the
FROM clause:

strFROM = "FROM table IN '" & DestConnectStr & "'"

Again I'm not sure, but you probably want to add
;DATABASE=path
in there too.

Or maybe it would be better to use one of the alternate ways
to specify an external connection:

strFROM = "FROM table IN '" & DestConnectStr & "' '" _
& path & "'"
or you might prefer this kind of syntax:
strFROM = "FROM [" & DestConnectStr & "].table "

I find this kind of stuff to be rather confusing, so be sure
to double check Help for all the options you can use in the
FROM clause.

 

[Marshall Barton]

thank you for your help. I also tryed the "In" approach.
But this application is installed to many users with
differnt DNS on there machines. The database parameters
are passed by an ini file. So I have to add the "IN"
stuff at runtime to the query. Do you know how I can do
that with VBA?

Assuming you can get an IN phrase to do what you need, you
can always modify a query. There are a few different ways
of storing queries, so the code will vary accordingly. If a
query is a saved query (in the DB Window query list), then
you can update the query's SQL property:

strSQL = "SELECT ... FROM table IN ..."
Set db = CurrentDb()
db.QueryDefs!queryname.SQL = strSQL
Set db = Nothing

I would put that kind of code in a sub procedure that would
I would execute at either install time or the db startup
form, depending on how dynamic the user's connection info
is.

If the query is an sql string in a form/report record source
then just replace it in the form/report's Open event
procedure:
Me.RecordSource = strSQL

If the query only exists in VBA code, then just modify the
code to change how the strSQL string is constructed.

Essentially, I'm saying that all of the remote access
queries will end up being constructed in VBA code.

I think you may be able to use an altogether different
approach that may be less disruptive of your existing app's
structure. Try just linking to the remote tables. You
should then be able to leave all of your existing queries
and SQL statements alone and only modify the linked table's
Connect property.

db.TableDefs!table.Connect = strConnectString

OTOH, I could be all wet and you would be better off using
ADO.

I think I'm just wandering around your question without
helping. If someone more experienced in this stuff doesn't
jump in and straighten this out, you might try posting your
question to a newsgroup that's more specific to your issue?

 

[Ludwin]

Hello Marsh,
thank you for all this good suggestions. I like the last
one most!
So placed in my login-window a variable of type database
and assigned to it currentDb().
Setting the connect works as I could see in the debugger,
but when my main Form opens this setting is lost. As fare
as I could see in the debugger the db Variable is not a
pointer to the CurrentDB but an own object. Do I have the
chance to adress the "main" DB? Or is there a way (shoud
be in an event) to do the setting when the Main form
opens. This event must appear before the imbeded
listfields are executed?

Thanks for your ongoing help!
Ludwin

 

[Marshall Barton]

I like the last one most!
So placed in my login-window a variable of type database
and assigned to it currentDb().
Setting the connect works as I could see in the debugger,
but when my main Form opens this setting is lost. As fare
as I could see in the debugger the db Variable is not a
pointer to the CurrentDB but an own object. Do I have the
chance to adress the "main" DB? Or is there a way (shoud
be in an event) to do the setting when the Main form
opens. This event must appear before the imbeded
listfields are executed?

Your db variable is just a temporary database object that
your VBA code can use to refer to the objects in your
database, in this case the TableDefs collection. The db
variable itself won't have any effect on opening forms, etc.

Once you have a table linked to the Oracle database, can you
open it directly from the database window without being
prompted? Once you get this working, the forms should
pretty much take care of themselves.

 

[Ludwin]

Hi Marsh,
Your assumption is right. When I once entered the Oracle
password it connects and I am not asked any more.
Can I simulate this in VBA with no visual effects?

-----Original Message-----

I like the last one most!
So placed in my login-window a variable of type database
and assigned to it currentDb().
Setting the connect works as I could see in the debugger,
but when my main Form opens this setting is lost. As fare
as I could see in the debugger the db Variable is not a
pointer to the CurrentDB but an own object. Do I have the
chance to adress the "main" DB? Or is there a way (shoud
be in an event) to do the setting when the Main form
opens. This event must appear before the imbeded
listfields are executed?

Your db variable is just a temporary database object that
your VBA code can use to refer to the objects in your
database, in this case the TableDefs collection. The db
variable itself won't have any effect on opening forms, etc.

Once you have a table linked to the Oracle database, can you
open it directly from the database window without being
prompted? Once you get this working, the forms should
pretty much take care of themselves.
--
Marsh
MVP [MS Access]

accordingly.
If a
issue?

.

 

[Ludwin]

Hi Marsh,
Thanks for your help!!!
I opened the database with a connectstring to Oracle,
opened a recordset: And now it works!

Thank you again!!
Ludwin

-----Original Message-----

I like the last one most!
So placed in my login-window a variable of type database
and assigned to it currentDb().
Setting the connect works as I could see in the debugger,
but when my main Form opens this setting is lost. As fare
as I could see in the debugger the db Variable is not a
pointer to the CurrentDB but an own object. Do I have the
chance to adress the "main" DB? Or is there a way (shoud
be in an event) to do the setting when the Main form
opens. This event must appear before the imbeded
listfields are executed?

Your db variable is just a temporary database object that
your VBA code can use to refer to the objects in your
database, in this case the TableDefs collection. The db
variable itself won't have any effect on opening forms, etc.

Once you have a table linked to the Oracle database, can you
open it directly from the database window without being
prompted? Once you get this working, the forms should
pretty much take care of themselves.
--
Marsh
MVP [MS Access]

accordingly.
If a
issue?

.