Upsizing Access DB to sql server DB

[J Cunningham]

Hi,

We have an existing Access DB that we would like to
convert to an Access front-end, sql server back-end
setup. In my research I have seen use of ODBC and OLE DB
being used as connections between the front-end and back-
end. Does anybody have an opinion of which is better to
use? The DB will be used by about 3000 people with read-
only access. Only the DB admin will have the ability to
update/add records.

Also, does the upsizing wizard automatically configure
the ODBC between the Access front-end and sql server back-
end? If not, what are the steps for doing so? In the
Access help section, not much info is provided on the
ODBC.

Thanks in advance,

Jeff

 

[Tony Toews]

Hi,

We have an existing Access DB that we would like to
convert to an Access front-end, sql server back-end
setup. In my research I have seen use of ODBC and OLE DB
being used as connections between the front-end and back-
end. Does anybody have an opinion of which is better to
use?

I think but am not sure, that OLE DB is a layer on top of ODBC. Thus
another layer has to slow things down somewhat.

The DB will be used by about 3000 people with read-
only access.

Just curious. How are you going to ensure the 3000 users have the
latest copy of the FE?

Also, does the upsizing wizard automatically configure
the ODBC between the Access front-end and sql server back-
end? If not, what are the steps for doing so? In the
Access help section, not much info is provided on the
ODBC.

You can use DSN less connections and update the connect string in the
table definition yourself by using code.

I much prefer DSN-Less connections as it is one less thing for someone
to have to configure and one less thing for the users to screw up.

Using DSN-Less Connections
http://members.rogers.com/douglas.j.steele/DSNLessLinks.html
ODBC DSN-Less Connection Tutorial Part I
http://www.amazecreations.com/datafast/GetFile.aspx?file=ODBCTutor01.htm&Article=true
HOWTO: Use "DSN-Less" ODBC Connections with RDO and DAO
http://support.microsoft.com/?id=147875
ODBC DSN Less
http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm


Tables: Relink ODBC tables from code
http://www.mvps.org/access/tables/tbl0010.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