How to connect MDB front end to SQL Server Backend

[Gman063 via AccessMonster.com]

Background: I have 4 databases, 2 that are MDB and 2 that are ADP. The 2
MDB are split databases that link to MDB back ends. The 2 ADP databases are
linked to SQL server.

Problem: I want to use a MDB front end to connect to both the MDB backend and
the SQL Server so that I can combine data on reports. I know the best way to
do this is keep the current link to the MDB back end and create an ODBC
connection to the SQL Server. I have never done the later (ODBC connection to
the SQL Server) before and cannot find any literature to do this. Can come
someone either direct me to a book or some source of information that can
tell me how to do this, or can someone explain how to do this.

Thanks in advance.

 

[Sylvain Lafontaine]

If you want to create and use ODBC linked tables, you should ask this
question in the m.p.access.odbcclientsvr newsgroup.

You create an ODBC linked table to SQL-Server in exactly the same way that
you will do with a MDB backend: by using the File | Get External Data | Link
Table... | Files of type -> ODBC Databases.

Of course, you must use the proper login for SQL-Server and if you have an
antivirus that is blocking new ODBC connection such as NAV 2006 or 2007, you
must first deactivate the blocking protection before creating your
connection (you can reenable it later when the link has been created).

 

[Gman063 via AccessMonster.com]

Sylvain Lafontaine, thanks for your reply.

Ok, I can get to this point: "You create an ODBC linked table to SQL-Server
in exactly the same way that you will do with a MDB backend: by using the
File | Get External Data | Link Table... | Files of type -> ODBC Databases."

Problem I have is where do I get the DSN information? Would it be better to
use code and create a connection string in ADO? If so, what would be the
correct string?

If you want to create and use ODBC linked tables, you should ask this
question in the m.p.access.odbcclientsvr newsgroup.

You create an ODBC linked table to SQL-Server in exactly the same way that
you will do with a MDB backend: by using the File | Get External Data | Link
Table... | Files of type -> ODBC Databases.

Of course, you must use the proper login for SQL-Server and if you have an
antivirus that is blocking new ODBC connection such as NAV 2006 or 2007, you
must first deactivate the blocking protection before creating your
connection (you can reenable it later when the link has been created).

Background: I have 4 databases, 2 that are MDB and 2 that are ADP. The 2
MDB are split databases that link to MDB back ends. The 2 ADP databases

[quoted text clipped - 13 lines]

Thanks in advance.

 

[Sylvain Lafontaine]

Well, if you are already using MDB split databases, I don't see what's your
problem in doing the same thing with SQL-Server. If you want info on
connection strings, see:

http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Sylvain Lafontaine, thanks for your reply.

Ok, I can get to this point: "You create an ODBC linked table to
SQL-Server
in exactly the same way that you will do with a MDB backend: by using the
File | Get External Data | Link Table... | Files of type -> ODBC
Databases."

Problem I have is where do I get the DSN information? Would it be better
to
use code and create a connection string in ADO? If so, what would be the
correct string?

If you want to create and use ODBC linked tables, you should ask this
question in the m.p.access.odbcclientsvr newsgroup.

You create an ODBC linked table to SQL-Server in exactly the same way that
you will do with a MDB backend: by using the File | Get External Data |
Link
Table... | Files of type -> ODBC Databases.

Of course, you must use the proper login for SQL-Server and if you have an
antivirus that is blocking new ODBC connection such as NAV 2006 or 2007,
you
must first deactivate the blocking protection before creating your
connection (you can reenable it later when the link has been created).

Background: I have 4 databases, 2 that are MDB and 2 that are ADP. The
2
MDB are split databases that link to MDB back ends. The 2 ADP databases

[quoted text clipped - 13 lines]

Thanks in advance.