Access FE link to two SQL database using ODBC...

[Paul]

I have an Access FE with tables link to two SQL BE using ODBC connection. I
configured the two ODBC as the system DSN to the client workstation. With
the way Access FE database design, all the tables for the forms link to one
SQL database. Both SQL databses are used for the reports. The problem is
when opening report, a pop up dialog box asking the ODBC connection, a check
box for the "trust connection" or user need to type in the user name and
password for the "other" SQL database. I told the user just to select the
"trust Connection" and it works. However I like to get rid of this annoying
dialog window? Thanks.

 

[Graham R Seach]

Paul,

You can only have one BE database per DSN. Therefore, since the one for the
forms seems to work OK, you need to create another one for the BE to which
the reports connect. Then link in those tables.

Don't forget to tick the "Save password" checkbox on the [Link Tables]
dialog.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Paul]

Hi Graham:

Thank you for your reply. Do you mean creating another DSN for the other SQL
database in the client workstation which I did already? I also have another
Access FE ONLY for that SQL database and it is running fine. The problem is
when Access FE link to two SQL databases and that ODBC connection pop up
dialog window asking for the second SQL database connection. As I said there
is no problem if the Access FE linking to a single SQL database and I do not
believe that it is the SQL security since the other Access FE can link to
the second SQL database without a hitch.

Paul,

You can only have one BE database per DSN. Therefore, since the one for the
forms seems to work OK, you need to create another one for the BE to which
the reports connect. Then link in those tables.

Don't forget to tick the "Save password" checkbox on the [Link Tables]
dialog.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

I have an Access FE with tables link to two SQL BE using ODBC

connection.

I
configured the two ODBC as the system DSN to the client workstation. With
the way Access FE database design, all the tables for the forms link to one
SQL database. Both SQL databses are used for the reports. The problem is
when opening report, a pop up dialog box asking the ODBC connection, a check
box for the "trust connection" or user need to type in the user name and
password for the "other" SQL database. I told the user just to select the
"trust Connection" and it works. However I like to get rid of this annoying
dialog window? Thanks.

 

[Van T. Dinh]

I would check the settings for the DSN to the "other" SQL database on the
problematic work-station. IIRC, there is a CheckBox for "Trusted
Connection" and perhaps, it has been unchecked in error.