Using MSDE with Access 2000

[Lee Ottaway]

I'm working on a project where I'm gradually phasing out an Access
2000 application with one written in VB.NET 2003 using the same
database as a back end. The company wants to phase out Access
altogether in favor of SQL Server within about a year. I can't just
drop Access altogether because the users still have to enter most of
their data through it's front end, but neither do I want to code
everything in my .NET application using OLEDBConnection since they'll
be a lot of work to convert it all over to SQLConnection in a years'
time.

It occurs to me that the best solution would be to use SqlConnection
but connect to the Access database. Can this be done in any way? Is
there a better solution that I'm missing?

Lee

 

[Bart Schelkens]

Why not write your application like this :

'********************************************************************

' Programme : Private Function GetConnection

' Developer : Bart Schelkens

' Date : 2 June 2004

' Parameters: -

' Return : IDBConnection

' Purpose : creates a connection to the database

' Remarks : -

'********************************************************************

Private Function GetConnection() As IDbConnection

Dim lstrSoortDB As String

lstrSoortDB =
System.Configuration.ConfigurationSettings.AppSettings.Item("SoortDataBase")

Select Case lstrSoortDB

Case Is = "access"

Return GetAccessCon()

Case Is = "SQLServer"

Return GetSqlCon()

Case Else

Throw New Exception(lstrSoortDB & " is invalid!")

End Select

End Function

'********************************************************************

' Programme : Private Function GetAccessCon

' Developer : Bart Schelkens

' Date : 2 June 2004

' Parameters: -

' Return : OleDBConnection

' Purpose : returns a connection to the access-database

' Remarks : -

'********************************************************************

Private Function GetAccessCon() As OleDbConnection

Return New
OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings.Item(
"conAcc"))

End Function

'********************************************************************

' Programme : Private Function GetSqlCon

' Developer : Bart Schelkens

' Date : 2 June 2004

' Parameters: -

' Return : SqlConnection

' Purpose : returns a connection to the SQL-database

' Remarks : -

'********************************************************************

Private Function GetSqlCon() As SqlConnection

Return New
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings.Item("c
onSql"))

End Function



And in your app.config you write :

<appSettings>

<add key="conAcc" value="provider=microsoft.jet.oledb.4.0;data
source=C:\Documents and Settings\Bart Schelkens\Mijn
documenten\VBNET\DSS_Administration\Database\DSSApp.mdb"></add>

<add key="conSQL" value="data source=PC12-09\VSDOTNET;initial
catalog=Nieuwsbrief;user id=sa;integrated security=true;"></add>

<add key="SoortDataBase" value="access"></add>

</appSettings>