By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,224 Members | 1,460 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,224 IT Pros & Developers. It's quick & easy.

Access 2.0 link to Sql Server

P: n/a
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 Databaseclick
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
Aug 18 '08 #1
Share this Question
Share on Google+
12 Replies


P: n/a
Hello Mourad,

The easiest fix for your problem is to upgrade to a more current version
of Access like Access 2003 (the workings of Access 2007 are considerably
different - they (MS) don't even call it an mdb anymore).

If you are working with current (or somewhat current) server technology
like sql2005 then you should be using a front end that is also current
(at least within the same century - Access2003 or .Net).
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 18 '08 #2

P: n/a
On Mon, 18 Aug 2008 13:51:19 -0700 (PDT), Mourad <mo************@gmail.com>
wrote:
>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 Databaseclick
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.
--

John W. Vinson [MVP]
Aug 18 '08 #3

P: n/a
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
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.comwrote in message
news:ri********************************@4ax.com...
On Mon, 18 Aug 2008 13:51:19 -0700 (PDT), Mourad
<mo************@gmail.com>
wrote:
>>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 Databaseclick
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.
--

John W. Vinson [MVP]

Aug 19 '08 #4

P: n/a
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" <mo************@gmail.comwrote in message
news:55**********************************@k7g2000h sd.googlegroups.com...
>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 Databaseclick
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


Aug 19 '08 #5

P: n/a
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
Aug 19 '08 #6

P: n/a
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

On Aug 19, 7:28*am, Mourad <mourad.bara...@gmail.comwrote:
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
Aug 19 '08 #7

P: n/a
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

Aug 19 '08 #8

P: n/a
"Larry Linson" <bo*****@localhost.notwrote in
news:QRqqk.197$w51.67@trnddc01:
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 W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 19 '08 #9

P: n/a
Mourad,

please ignore aaron.

aaron is a troll, and posts irrelevant, insulting or erroneous
information
aaron should go away.

"aa r o n . k e m p f @ g m a i l . c o m" <aa*********@gmail.com>
wrote in
news:cb2098fe-8b26-4ca6-9521-9ac516310de3
@a3g2000prm.googlegroups.com
:
too solid?
Well, how about perfectly solid, as opposed to the diarhea that
comes out of your mouth aaron.

jet always corrupts!!
no, aaron, well written Access apps, unlike yours, don't often
corrupt.

--
Bob Quintal
strThisMsg = replace(strThisMsg,"aa","mo")
PA is y I've altered my email address.
** Posted from http://www.teranews.com **
Aug 19 '08 #10

P: n/a
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" <mo************@gmail.comwrote in message
news:5e**********************************@m73g2000 hsh.googlegroups.com...
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

Aug 20 '08 #11

P: n/a
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

Aug 20 '08 #12

P: n/a
Hello again,

If time is money for you, one other possibility would be to export your
data to either text or Excel files and then import the data from those
files to the sql server.

Note: for sql server 2005 I have had better luck importing from Excel
than from text files when using the import wizard (but I have had the
best luck/performance writing my own import routines in .Net).

If you are using Access 2.0, the data tables couldn't be too big. Even
if you have several little Access 2.0 mdbs with data, I would export the
data to Excel (or to a text file and then import the text file to Excel)
and then import the Excel files to your sql server.

If this is tedious, then the question would be which is more tedious -
trying to find an ODBC driver that will work with Access 2.0 and sql
server 2005 (which you still have to export the data) or exporting the
data to the Excel files and then importing the files to the server?

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 20 '08 #13

This discussion thread is closed

Replies have been disabled for this discussion.