ado connection to access mdb file.

udikantz

hi guys, i am developing an application which uses access mdb files as database. untill now i used a connection to the files, using the following code:

Public Class DBconnector

Private \_dbConnection As OleDbConnection
Private \_dbCommand As OleDbCommand
Private \_dbDataReader As OleDbDataReader
Private \_dbDataAdapter As OleDbDataAdapter
Private \_dbTransaction As OleDbTransaction
'Private \_dbADODBConnection As ADODB.


Public Sub New(ByVal cs As String)

    \_dbConnection = New OleDbConnection(cs)
    \_dbCommand = \_dbConnection.CreateCommand

End Sub

where the connection string is:

"Provider=Microsoft.Jet.OLEDB.4.0;"
& "Data Source=" & TextEditPath.Text

texteditpath is the file path and name. recently i have encountered an error while trying to create a relationship using "ON DELETE CASCADE" in the constraint clause. error mentioned theres a syntax error on the constraint clause and there was no real syntax error , after troling microsoft forums , they have told me i need to use ADO connection because this bug results cause of use in DAO , my question is how do i establish an ADO connection to access MDB file you can see my discussion in the ms forums here: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?pg=2&cat=en_us_328f8dd6-f51c-496e-93d7-5d47a17e195c&lang=en&cr=us&guid=&sloc=en-us&dg=microsoft.public.access.queries&fltr=[^] under the title : "add constraint return syntax error"

Net

Dave Kreskowiak

This is a question for the General Database forum... Did the command line to test the command work in Access 2007 in the Immediate window?? And you're already using ADO.NET. Using DAO (really, really, really old!) never happens in VB.NET unless you go out of your way to explicitly add DAO to the project and write the code to use it.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008

udikantz

yes, in the command line the Query works im executing the following query:

CurrentProject.Connection.Execute "ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY ([calendarid]) REFERENCES Calendar ([id]) ON DELETE CASCADE"

although in the SQL designer window of access 2007 the following query wont work:

ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY ([calendarid]) REFERENCES Calendar ([id])

generate same error as i get in the oleDB connector withing the .net framework: syntax error in constrain calause i dont know how to set a connection to the file so this query will work as it works in the immidiate window

Net

udikantz

mistake in my post the sql in design window is:

ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY ([calendarid]) REFERENCES Calendar ([id]) ON DELETE CASCADE

Net

paas

If you want to establish an ADO connection to an Access MDB file in .NET, you will need to use COM Interop and start by setting a reference to Microsoft's ActiveX Data Objects library. This comes up often, and I guarantee if you google activex data objects "Com Interop" you will find examples.

udikantz

hey, I have established an ado connection and excuted the query through the connection resulted with the same error as the oleDB i used the following code:

Public Sub adoUpdate(ByVal ct As String)
Dim con As New ADODB.Connection()
con.ConnectionString = _dbConnection.ConnectionString
con.Open()
con.Execute(ct)
con.Close()

End Sub

_dbConnection.ConnectionString contains the same connection string that i mentioned in this thread. im really clueless need some more help pelase , thanks for all the help !!!

Net

udikantz

need help here

Net

udikantz

.

Net