ASP to Linked Access MSSQL2005 table (And ASP)

[garddawg]

Ok this one might be fun or interesting for all you die hard MS access
users.

I have a legacy ASP web application that uses Access as it's Database.
The database is getting very large 500MB and it is become very slow. I
would like to upgrade to MSSQL 2005. My thought was to make this
change gradually and continue to use Access as the front end for the
Database. I was thinking that I could move the larger tables from
Access to MSSQL and then create a linked table in Access to access
these tables.

When I attempt this I get the following error.
[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to
'SQL_DSN_NAME' failed.

The error message indicates to me that the ASP app is trying to access
the SQL database using the MS Access ODBC Driver. But I don't know.

Does anyone know if this is possible?
If so how can it be done?

Thanks



|-----------------------------|
| Web application |
|-----------------------------|
|
|
|
|
\ /
|-----------------------------|
| Access |
|-----------------------------|
|
|
|
|
\ /
|-----------------------------|
| MSSQL |
|-----------------------------|

 

[dbahooker]

dude you're whacked.. throw out the MDB and connect directly to SQL
Server

-aaron

 

[dbahooker]

|-----------------------------|

| Web application |
|-----------------------------|
|
|
|
|
\ /
|-----------------------------|
| MSSQL |
|-----------------------------|
/ \
|
|
|
|
|-----------------------------|
| Access |
|-----------------------------|

Ok this one might be fun or interesting for all you die hard MS access
users.

I have a legacy ASP web application that uses Access as it's Database.
The database is getting very large 500MB and it is become very slow. I
would like to upgrade to MSSQL 2005. My thought was to make this
change gradually and continue to use Access as the front end for the
Database. I was thinking that I could move the larger tables from
Access to MSSQL and then create a linked table in Access to access
these tables.

When I attempt this I get the following error.
[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to
'SQL_DSN_NAME' failed.

The error message indicates to me that the ASP app is trying to access
the SQL database using the MS Access ODBC Driver. But I don't know.

Does anyone know if this is possible?
If so how can it be done?

Thanks



|-----------------------------|
| Web application |
|-----------------------------|
|
|
|
|
\ /
|-----------------------------|
| Access |
|-----------------------------|
|
|
|
|
\ /
|-----------------------------|
| MSSQL |
|-----------------------------|

 

[Norman Yuan]

Why does your ASP connect to a linked Access, instead of to SQL Server
directly? The only reason the Access exists is that you need some sort of
front end in the Access, which is not a web application. As for the ASP, it
should directly connect to the SQL Server.

 

[Bill Mosca, MS Access MVP]

My experience tells me that you must change your connection on the ASP pages
to go directly to the SQLS db.

I've done what you want...keeping the Access db as a front end, but I
migrated all the tables to SQLS

 

[ben h]

When I attempt this I get the following error.
[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to
'SQL_DSN_NAME' failed.

Use a DSN-less connection string to connect to your db. You'll want to
use the OLE provider, see:
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer

for some more information. Alternatively, http://aspfaq.com is the
premier site for all things ASP & MSSQL Server.

Cheers,
Ben

 

[garddawg]

Thanks for the help.

When I attempt this I get the following error.
[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to
'SQL_DSN_NAME' failed.

Use a DSN-less connection string to connect to your db. You'll want to
use the OLE provider, see:
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer

for some more information. Alternatively, http://aspfaq.com is the
premier site for all things ASP & MSSQL Server.

Cheers,
Ben

 

[dbahooker]

this is probably going to surprise you guys-- that I'm actually saying
SOMETHING positive about MDB.. so here goes:

for the record; there is a time when linked tables to SQL Server in a
MDB really helps to simplify and it does provide decent performance..
and it's awfully simliar to this so I thought that it's worth a mention

for a simple ASP app; it's 'not necessary' and 'overly complex' but
there is a time and place to do something like this

if you're processing an analysis services cube from multiple sql
database servers; or for example-- joining oracle and teradata or
something ridiculous like that-- then you can use ODBC linked tables in
a MDB to really simplify what you're doing.

1. build a linked table to teradata
2. build a linked table to sql server data
3. point your SSAS datasource against the MDB and process away.

it does seem counterintuitive to point a DB Server to a MDB that links
to other servers; but in some places; it does and it SHOULD work.

Thanks

-Aaron

Thanks for the help.

When I attempt this I get the following error.
[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to
'SQL_DSN_NAME' failed.

Use a DSN-less connection string to connect to your db. You'll want to
use the OLE provider, see:
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer

for some more information. Alternatively, http://aspfaq.com is the
premier site for all things ASP & MSSQL Server.

Cheers,
Ben