Access 2.0 link to Sql Server

[Mourad]

I need to attach (link) a table in SQL Server 2005 database to an
Access 2.0 front-end database:

On Access 2.0: Click File\Attach Table..., select <SQL Database> click
OK

On the "SQL Data Sources" dialog box, however, I cannot see a "SQL
Server" selection!!!

So' I'm unable to link!

I hope my question is clear.

Thanks for reading,

Mourad

 

[John W. Vinson]

I need to attach (link) a table in SQL Server 2005 database to an
Access 2.0 front-end database:

On Access 2.0: Click File\Attach Table..., select <SQL Database> click
OK

On the "SQL Data Sources" dialog box, however, I cannot see a "SQL
Server" selection!!!

So' I'm unable to link!

I hope my question is clear.

Thanks for reading,

Mourad

2.0!?!

That's *fifteen years old*!!! Do you drive a 1937 Cord?

I don't currently have it installed (yes, I still have the ten or twelve
diskettes) but try looking for "ODBC Connection" instead of SQL/server. You
will need to define an ODBC Connection in Control Panel... Adminiistrative
Tools... Data Sources.

 

[Larry Linson]

I worked on an Access 2.0 client application to Informix. The DBA used a
third-party ODBC Driver (which name I forget at the moment, but I remember
the company was acquired by another) that worked very well. On the other
hand, I _do_ wish I owned a 1937 Cord! But, I'm not sure whether any
company still sells drivers for Access 2.0... nor whether one that Microsoft
would (probably) still have somewhere would work with SQL Server 2005.

I haven't tried it, but am told that Access 2.0 will not run on a machine
with more than 1GB memory (or was it 2GB). The only machine on which I
still have Access 2.0 installed is a vintage-1999 PII 450 with 256MB. It
was good, solid software in its day, and still is, if security is not an
issue. I have also been told if your machine is a "big mama" with lots of
memory and you need to run Access 2.0, you can create a virtual machine with
less memory so it doesn't crash.

Larry Linson
Microsoft Office Access MVP

 

[david]

You need 16 bit ODBC drivers, and the 16 Bit ODBC framework.

Get your Access 2.0 install disks, and re-install the ODBC drivers.

Open the 16-bit ODBC control in Control Panel, and create a
File DSN for your SQL Server.

(david)

 

[Mourad]

Thanks everyone for your feedback, much appreciated.

Rich, this is in fact a part of the upgrade process. I agree with you,
Access 2.0 is too old, but too solid as well.

With so many front-end's and back-end databases, making a HUGE system,
the upgrade is far from simple! It has to be done gradually, one
database at a time, then it comes the problem of having mixed version
front-end's with links to an A2.0 back-end database! So one solution
is to first move all back-end's to Sql Server, so to allow A2.0 and
A2003 front-ends working together on same data.

David,
Thanks for your feed back, I'm going to give it a shot! I've read also
that having a full/complete installation of A2.0 will show the "SQL
Server" selection on the "SQL Data Sources" dialog box.

Best,

Mourad

 

[a a r o n . k e m p f]

too solid?

jet always corrupts!!

move to ADP and things work much more reliably
no horsing around, no installation problems

plug and play simple dev / deploy against SQL Server

 

[Mourad]

David,

Thanks you for the advice.
I installed the ODBC Administrator from Access 2.0, then setup a new
SQL Server data source. And now I can see the data source from Access.

This is a great progress.

I still need to find out why the login fails:

Connection failed:
SQLState '08S01'
SQL Server error: 0
Coomunication link failure.

Thanks again for reading,

Mourad

 

[David W. Fenton]

I worked on an Access 2.0 client application to Informix. The DBA
used a third-party ODBC Driver (which name I forget at the moment,
but I remember the company was acquired by another) that worked
very well. On the other hand, I _do_ wish I owned a 1937 Cord!
But, I'm not sure whether any company still sells drivers for
Access 2.0... nor whether one that Microsoft would (probably)
still have somewhere would work with SQL Server 2005.

ODBC is ODBC. If Access 2 can use ODBC drivers, any ODBC driver
should work. Whether or not it gives adequate control of the
database its connecting to depends on how well the ODBC driver is
written, not on Access itself.

 

[david]

Can you login and test from the ODBC administrator?
Do you have an old 16 bit copy of Excel you can use
to test with?

(david)

 

[Mourad]

David,

Thanks for the follow up. There are some progress: I upgraded the 16
bit ODBC drives using those come with Visual Studio 6.0 E/E, then
setup an ODBC data source (Administrative Tools\Data Sources (ODBC) in
the control panel, not the ODBC) then I could successfully attach
tables from SQL Server 2005.

So the problem was the old ODBC drivers, as you had mentioned earlier.

Thanks again for the input,

Best

Mourad