473,322 Members | 1,510 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Access 2.0 link to Sql Server

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
12 2716
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
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
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
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
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
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
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
"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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: ErickR | last post by:
We are experiencing a problem with Sql Server 2000 linking to an Access 97 file. We have two machines that link to this .mdb file, and we recently upgraded one to newer hardware, SP3a, MDAC 2.8,...
8
by: Dominic Martin | last post by:
Hi, I need to setup an ODBC link over our Internet connection between MS Access (local) and a MySQL server (Remote - the local PC has the MySQL ODBC driver installed). What kind of bandwidth...
5
by: premmehrotra | last post by:
I currently have a multi-user access database which is put on a shared drive L: on a Windows Servers. Entire database is one file premdb.mdb. Users access this database from their laptops....
2
by: ugo_lavoie | last post by:
I mean how does it work. I'm new to this and i cant find good information. Access is the front end (forms..report...etc), and sql the database right?? Do i need a dedicated server for that. Stuff...
2
by: egoldthwait | last post by:
I need to convert a 17mb access 2000 db to Oracle and house it in a Citrix farm. The issue: we have never converted an Access Db to Oracle but can probably use Oracle's Workbench to assist with...
7
by: needin4mation | last post by:
Hi, I have an Access 2002 - 2003 database. I am using Access 2003. Whenever I link an image all it shows is the filename. Not the image. Other versions of Access can link the image just fine. ...
9
by: Wayne Smith | last post by:
I've come up against a major headache that I can't seem to find a solution for but I'm sure there must be a workaround and I would really be grateful of any help. I'm currently building a web...
6
by: onnodb | last post by:
Hi all, While working on an Access UI to a MySQL database (which should be a reasonable, low-cost, flexible interface to the DB, better than web-based, much less costly than a full-fledged .NET...
0
by: NissanSE98 | last post by:
Hello, I'm building an asp.net application that will get contact information from an Access Database that is linked to microsoft exchange or outlook. I go to link tables in Access and choose...
2
by: Bobby | last post by:
Hi, Not sure if this is Access, SQL or ODBC. I have a SQL database with an Access Front End. They are linked with ODBC. Occasionally (it's happened 3 times in 4 months) some of the linked tables...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.