Reaching Access 2003 datas via SQL Server 2005 64 bits

[Fred]

Hi group,

I would like to be able to reach a Access 2003 database via an Sql
Server 2005 64 bits.

(I have no problem to reach Sql Server 2005 tables from Microsoft
Access 2003 database.)

It seems that it's a rather hard issue. I have seen in some forums
talking about Microsoft SQL Server Express to do the link between both
databases. Unfortunately for I can't set up this product (Microsoft
SQL Server Express) on the server that host SQL Server
Is there another issue I could go with in order to have both Databases
running. (SQL Server 2005 reaching Access 2003, just for reading)

Is migrating Access 2003 to Access 2007 could be a solution?

Thanks for all the answers you could provide me.

Best regards,

Fred

 

[Sylvain Lafontaine]

First, this newsgroup is about ADP and has nothing to do with the other
formats of Access database files known as MDB and ACCDB and/or Access JET.
You will have more chances of getting a more appropriate answer to your
questions by posting to a appropriate newsgroup.

To come back to your case, the answer is simple: you can't reach an Access
2003 or 2007 database (so migrating from 2003 to 2007 is not a solution)
directly from a SQL-Server - or any other program - running under the 64
bits because at this time, there is no 64 driver for both the ODBC and the
OLEDB Providers for Access/JET and you cannot do anything about that other
than installing some kind of program running in 32 bit mode and used as a
mid-layer between these two. (Using SQL-Express in 32 bit mode for this
purpose is usually what's suggested the most often in the newsgroups.).

As it is only for reading, the most simple solutin would be to simply upload
the data to table on SQL-Server from the Access application. You could also
have an external client (such as a SSIS package or any other kind of scripts
or of programs) that will check regularly if a new version of the MDB/ACCDB
database file exists and if yes, that will copy the data from the Access
database file to the SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Paulo Norberto]

Hello Sylvain,

Thank you for your advice. I will correct it to avoid using the identity
field (it was the easy way to explain my problem).
Nevertheless, even with the procedure to create the invoice number I still
have the initial problem! How can I display the results of the fields
associated to the value selected in the combo box without saving the record?

Like:
One client enters in the store and asks for a product. The salesmen start a
new invoice and one of the fields that he has to fill is a combo box. The two
other fields are related to the combo box. If I don’t save the record I can’t
see values in those two fields. If I save the record I will see values in
those two fields.
When the salesmen show the result to the client, the client decides that he
doesn’t want the products. Now the salesmen will have to cancel the invoice
(delete the record).
If there is any way to show those values without saving the record it will
not be needed to delete it.
This is a fictitious situation but in my real process almost 50% of the
contracts (I do loan contracts not invoices) are canceled when clients see
the values that have to pay.

Thank you.

Regards,