asked on

Error connecting VB.net Application to an Access DB on C:\ Drive

Hi all, i would very much appreciate anyone that can help me on this...

I have a VB.net app that talks to an SQL database fine on our network - all my connection strings work and are fine. I now need the app to talk to an Access 2007 DB that is located on my C:\ drive instead of accessing the network DB.

All of the connection strings i have tried for access just will not work! please can you see where i am going wrong? I dont really know whether i need to set up DSNs?? I have nothing set up at the minute, all i know is that the supplied code works.

Thanks!
Craig.

'CURRENT DB CONNECTION:
conn = New ADODB.Connection
rs = New ADODB.Recordset
 
conn.Open(DRIVER=SQL };SERVER=192.168.X.X;DATABASE=XXX;UID=XXX;PWD=XXX;")
sql = "SELECT * FROM admin;"
rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockPessimistic)
rs.MoveFirst()
'DO SOME STUFF WITH THE DATA

Open in new window

Jorge Paulino

Uou should use OleDBConnectionString, from the namespace System.Data.OleDB, instead of ADODB
Also your connection string must be changed to this (examples) : http://www.connectionstrings.com/?carrier=access2007

craignorris28

ASKER

thanks can you give me a code example?

Jorge Paulino

Here's an example I have post it and it's for SQL databases. To change to Access you just have to replace SQL... with OleDB..
Example SqlClient with OleDb, SqlCommand with OleDbCommand, SqlDbType with OleDbType, etc.
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_23349839.html

Just let me know if you have problems using it.

craignorris28

ASKER

sorry i dont get it, im not that advanced - can you tell me what References i need to add, and a code example of what i should paste in my code? thanks

Jorge Paulino

Ok, here's an example

Imports System.Data.OleDb
 
Public Class Form1
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;"
        Dim SQL As String = "SELECT * FROM myTable"
 
        Using connection As New OleDbConnection(connString)
            Dim command As New OleDbCommand(SQL, connection)
 
            ' Opens the connection
            connection.Open()
 
            ' Reads the information from the db
            Dim reader As OleDbDataReader = command.ExecuteReader()
            While reader.Read()
                Debug.WriteLine(reader.Item("ID"))
            End While
 
            ' Closes the connetion
            reader.Close()
        End Using
 
    End Sub
 
End Class

Open in new window

craignorris28

ASKER

ok thanks - so if im converting my app to use local Access rather than ADODB SQL, its not as simple as just changing the connection string? i need to remove references to .fields etc too?

ASKER CERTIFIED SOLUTION

Jorge Paulino

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

craignorris28

ASKER

thank you for your help!