Access and SQL Server

  • Thread starter Thread starter Stan
  • Start date Start date
S

Stan

I want to link an Access mde to SQL server using an odbc
interface (I don't want to go through the trouble of using
an ADP, as using the link I can using most of my VBA code
(a lot of DAO)). Is this viable. Any thoughts on this
would be appreciated.
Thanks,
Stan
 
Stan said:
I want to link an Access mde to SQL server using an odbc
interface (I don't want to go through the trouble of using
an ADP, as using the link I can using most of my VBA code
(a lot of DAO)). Is this viable. Any thoughts on this
would be appreciated.
Thanks,
Stan
Click to expand...

Just think about it this way. People have been writing Access apps against SQL
Server since way before ADPs even existed. What method do you suppose they used
instead? In addition, people write Access apps against server-based engines
besides SQL Server (which is the only one that an ADP works with). What methods
do you suppose they use when they do that?

Now this isn't to say that all you have to do is move your tables from Access to
SQL Server, link to them, and then carry on as if nothing will need to be
changed, but in general ODBC linked tables work very well.
 
Stan said:
I want to link an Access mde to SQL server using an odbc
interface (I don't want to go through the trouble of using
an ADP, as using the link I can using most of my VBA code
(a lot of DAO)). Is this viable. Any thoughts on this
would be appreciated.
Click to expand...

Works well. It can be a pain to convert depending on how much
conversion you want to do though.

Do a search at the Knowledge Base at support.microsoft.com using the
keywords "upsizing" to review the various white papers on upsizing
Access to SQL Server as well as to ensure you have any updates
required.

Also see my Random Thoughts on SQL Server Upsizing from Microsoft
Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Microsoft actually recommends Access *Database* (MDB/MDE)
with ODBC-linked Tables as the preferred method, not ADP.

However, if you are converting from an Access Back-End to
an SQL Server Back-End, you will probably need to modify
existing VBA codes slightly, especially if you use a lot
of Recordset codes since you often need to use
the "dbSeeChanges" option with Recordsets created from SQL
Server Back-End. Also note that if you use the AutoNumber
Field (Identity Field in SQL Server), the value is NOT
allocated (unlike JET Back-End) until the Record is
updated into the Table.

Use DAO codes in the mean time but other experienced
developers previously recommended converting to ADO codes
when time permitted.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top