Advise on connections to Access and SQL

[HebieBug]

Have a project at the moment the system is an Access 2000 backend and am about to create a VB front end.
We are moving the backend database from Access to SQl in 6 months.
Have seen there is a number of connection methods to a database. Our front end does not have the navagation bar and we do not intend our users to be able to use this.
Anyone have any advise on what connection method we should use. Keeping in mind that when we change the backend we do not ready want to have to change the code around to much.

 

[Qaroven]

Well, how bout you use Access 2000 project for the front end and SQL Server for the backend? They work quite well together in my experience and the main downers are just that each client has to have Access installed and a few Access-ish limitations. Ben
+61 403 395 052

 

[bbosley]

I would use ADO. You only need to change the connect string of the connection object when you go to SQL Server. Up front planning can save a lot of time later on.

Hope this helps.

Bryan Bosley

 

[Terpsfan]

A few tips for creating a connection to SQL Server.
1) create DSN-less connections (you don't want to access the ODBC driver if you don't have to)
2) use ADO (activeX data objects) as your data access method.
For example
Dim objConn As ADODB.Connection
Dim objRec As ADODB.Recordset
set objConn = New ADODB.Connection
Set objRec = New ADODB.Recordset
'Create a DSN-less connection
objConn.ConnectionString = "PROVIDER = SQLOLEDB.1;Data Source = SQLSERVERNAME; Initial Catalog = Databasename;UID=sa;PWD="
objRec.Open = "Select...sql statement"
Then when you close the form, close your connection in form unload. Otherwise your connection will be running in memory.
objRec.Update
objRec.Close
Set objRec = Nothing
objConn.Close
Set objConn = Nothing