What's the steps to modify a .adp connect string & convert DAO to

  • Thread starter Thread starter Mander
  • Start date Start date
M

Mander

What are the steps needed to modify a .adp (Access Data Project) file
connection string to point to a SQL Server database? Also how and where
do I go to convert the DAO (Data Access Object) code to it's equivalent
ADO (ActiveX Data Objects)? I'm converting a MS Access2007 database project
to SQL Svr2005 back-end and wish to have Access2007 remain as the front-end
aplication. Please note, I have used the Upsizing Wizard to create the .adp
file to create a Client - Server application.
 
Mander said:
What are the steps needed to modify a .adp (Access Data Project) file
connection string to point to a SQL Server database?
Click to expand...

In code or by hand? In code, you must use the
CurrentProject.CloseConnection and CurrentProject.OpenConnection methods.

Also how and where
do I go to convert the DAO (Data Access Object) code to it's equivalent
ADO (ActiveX Data Objects)?
Click to expand...

You must do the conversion manually. The Upsizing Wizard will only create
the tables on the SQL-Server backend, migrates the data and possibly convert
some of your queries but only a handful of them. For the rest, you must do
it yourself.

A possibility would be to create the equivalent of a CurrentDb using DAO;
this will give you the possibility of keeping a portion of your code as it
is already written but personally, I prefer to convert everything to DAO
when dealing with an ADP project.
I'm
converting a MS Access2007 database project
to SQL Svr2005 back-end and wish to have Access2007 remain as the
front-end
aplication. Please note, I have used the Upsizing Wizard to create the
.adp
file to create a Client - Server application.
Click to expand...

If you don't know ADP or if it is your first upsizing project, an easier and
simpler solution would be to upsize to a MDB or ACCDB database file with
Linked Tables.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
 
Here is an example of how to Change the connection string programmically
-------------------------------------------
'Close the current connection
Application.CurrentProject.CloseConnection
'Rebuild the new connection string
'The Provider, Data Source, and Initial Catalog arguments are
required.
strConnect = "Provider=SQLOLEDB.1" & _
";Data Source=" & strServerName & _
";Initial Catalog=" & strDBName
If strUN <> "" Then
strConnect = strConnect & ";user id=" & strUN
If strPW <> "" Then
strConnect = strConnect & ";password=" & strPW
End If
Else 'Try to use integrated security if no username is supplied.
strConnect = strConnect & ";integrated security=SSPI"
End If
'Reconnect on the new connection string
Application.CurrentProject.OpenConnection strConnect
--------------------------------

ADP's are not for the weak at heart, if you are not comfortable with writing
VBA or are not familiar with ADO, then the DAO and MDB's might be right for
you.

Good luck.
 
Back
Top