linking from access to SQL tables

[Ted]

Hi all, this is a total newbie question with SQL Server. We have an App that
uses Access as the backend. We are going to get an update which would mean
our backend tables would be stored in SQL Server instead of Access.

my question...I have an Access db that I use for reporting & its linked to
all the tables in Access. How difficult is it to link to the tables in SQL
Server and is it clean? Any problems? I know its probably a no brainer but I
haven't touched SQL Server in 9 years.

TIA
Ted

 

[Albert D. Kallal]

Hi all, this is a total newbie question with SQL Server. We have an App
that uses Access as the backend. We are going to get an update which would
mean our backend tables would be stored in SQL Server instead of Access.

my question...I have an Access db that I use for reporting & its linked to
all the tables in Access. How difficult is it to link to the tables in SQL
Server and is it clean? Any problems? I know its probably a no brainer but
I haven't touched SQL Server in 9 years.

Assuming that your front end was linking to a backend database, and now you
plan to link to SQL server, you'll actually find not much difference in the
whole process.

Assuming you have the correct permissions and logon ID to get to SQL server,
you'll simply just delete your table links that you have now, and then go
new->link and choose ODBC, and then create a file DSN that simply alllows
you to link to sql server. I suspect that people helping you with this
application upgrade can also give you some information that will help you
link to sql server, but once linked your front end will behave really the
same as before....

 

[Klatuu]

Acutally not that hard. The first time you do it, you will have to define an
ODBC DSN. Start as you normally would with File, Get External Data, Link.
At the bottom of the dialog, select ODBC Datatases() form the File of Type:
box.
You will get a Select Data Source dialog. To the right of DSN Name, select
New
A Create New Data Source dialog will open. Select SQL Server. From that
point forward, just follow the wizard to create the new DSN. Once created,
when you want to link to a table, just use the DSN name you created to link
to a table in the database.

 

[Ted]

just what i wanted to hear...thank you very much for the info

 

[Ted]

thank you for the info...that is great news.

Acutally not that hard. The first time you do it, you will have to define
an
ODBC DSN. Start as you normally would with File, Get External Data, Link.
At the bottom of the dialog, select ODBC Datatases() form the File of
Type:
box.
You will get a Select Data Source dialog. To the right of DSN Name,
select
New
A Create New Data Source dialog will open. Select SQL Server. From that
point forward, just follow the wizard to create the new DSN. Once
created,
when you want to link to a table, just use the DSN name you created to
link
to a table in the database.