Sqlconnection vs Oledbconnection and DB Access 2003

Question

I have seen some comments about the difference of sqlconnection vs oledbconnection.

However, it looks that SqlConnection is better and it is used to connect to "SQL Server 20XX" data bases.

What about if I want to connect to an Access2003 data base.

I would be able to use  sqlconnection?

Should I use sqlconnection instead of oledbconnection?

Thanks,

Enrique.

Answer 1

I have seen some comments about the difference of sqlconnection vs oledbconnection.

However, it looks that SqlConnection is better and it is used to connect to "SQL Server 20XX" data bases.

What about if I want to connect to an Access2003 data base.

I would be able to use  sqlconnection?

Should I use sqlconnection instead of oledbconnection?

Thanks,

Enrique.

Answer 2

Duplicate

Answer 3

I have seen some comments about the difference of sqlconnection vs oledbconnection.

However, it looks that SqlConnection is better and it is used to connect to "SQL Server 20XX" data bases.

What about if I want to connect to an Access2003 data base.

I would be able to use  sqlconnection?

Should I use sqlconnection instead of oledbconnection?

Thanks,

Enrique.

You cannot use SQlconnection to connect to MS Access2003 or higher version. that why microsoft have oledbconnection for you to connect to any access database. sqlconnection is developed to access sqlserver database.

---

Don't judge me, just Upgrade me. Thanks!

Answer 4

Hi Enrique,

in ADO.Net you have to differentiate between what is "generic" and what is provider-specific. That is, i.e. DataTable and DataSet are generic classes that are actually not connected to anyhting that relates to a specific database-system, like SQLS or Access - you can use these without any database, too.
The providers come into play when you want to access a database-system. Here you'll need the provider-specific classes, such as SqlConnection or SqlCommand (System.Data.SqlClient) when accessing a SQL Server database or their Access-counterplarts - OleDbConnection/OleDbCommand (System.Data.OleDb).

---

Cheers,
Olaf

Answer 5

Access uses the JET database engine and OLEDB connections are what I use for Access. Like Olaf said, different providers use different formats. SQLconnection is becoming more common. Generic Datasets are great for temporary data in which you do not need to store. If you have to use a specific Access database, I would create a typed dataset. A typed dataset will increase speed, not only in load time, but when you are writing code with Intellisense. Access is great for sharing data because many people have Microsoft Office and can manipulate the data, then again, you can save to a CSV and open in Excel, but in most cases you will have to write additional code to import the CSV file. Access is a great familiar user interface. SQL is vast! If possible, I would move to SQL if you are using data in a fixed location. You could always export your data in different formats and you can also have the features of a SQL typed dataset.

-dave

Answer 6

i fyou want to connect to MS ACCESS use oledb and if you want to connect to sqlserver u need sqlconnection. The good with sqlconnection string is that you can see the changes without close the application, am talking about sqlclient. with access 2003 you have to use oledb, you cannot use sqlconnection at all. but microsft is giving free sqlserver, whatver you do in ams access you do better in sqlsever database.

So, what I thin is, chalange you mind by using sqlserver database, and I think you get more help than 2003. All the best for you.

---

Don't judge me, just Upgrade me. Thanks!

Answer 7

No you cannot use a specific provider like SQLClient or OracleClient on a databasefile. 

OLEDB and ODBC are general providers, SQLClient and (by instance) OracleClient are meant as clients for specific database servers.

Be aware that Access is not a DataBase server, it is a DataBase file (Jet), this means by instance that you only can connect too it as a File.

SQLServer and Oracle can be called from everywhere in the world simple on their IP connection.

---

Success
Cor

Answer 8

There is no native .NET provider for Microsoft Access so you need to use either System.Data.OleDb (Jet or ACE OLEDB Provider) or System.Data.Odbc (MS Access ODBC driver). OleDb is recommended. Connection strings below:

http://www.connectionstrings.com/access
http://www.connectionstrings.com/access-2007

SQL Server has a native .NET provider (System.Data.SqlClient) but also has OLEDB and ODBC drivers and can be used with the OleDb and Odbc .NET libraries (mentioned above).

http://www.connectionstrings.com/sql-server-2008
http://www.connectionstrings.com/sql-server-2005

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 9

Thanks but I need to use Access DB because the DB already exist and is huge.