Updating database using ODBC

My code works fine when I use ADO controls to search through the database. However, I get an error when I try to update the database. I haven't been using ADO controls for updating, adding, or deleting, rather I use this code:

Dim conResult As New ADODB.Connection
Dim cmdResult As New ADODB.Command

conResult.mode = adModeShareDenyNone
conResult.CursorLocation = adUseClient
conResult.Provider = "Microsoft.Jet.OLEDB.4.0"
conResult.ConnectionString = "DSN=myDSN"
conResult.Open

Set cmdResult.ActiveConnection = conResult
cmdResult.CommandType = adCmdText
cmdResult.CommandText = strUpdate 'SQL Command
cmdResult.Execute

I get an error on the line that is in bold that says "Cannot find installable ISAM." I've tried this on several computers so I don't think it's something I missed installing Visual Studio. Also, when I replace the ConnectionString property with a connection string (the kind you build in the wizard when you select "Use Connection String") instead of a DSN, it works fine.

How can I update, add, and delete records using ODBC?? I want to use ODBC so I can connect over a network without the database path hardcoded into my code!

Oh yeah, and I'm using MS Access.

Thanks in advance for any help!!

Hope this helps...

I had a similar problem when I first created my app because I was using ADO controls. I am connecting to Access using the familiar connection string:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\

The original connection string is hardcoded in the control's connection string property.

You said that when you build it through the control you are able to connect without problems.

Now, instead of deleting the connection string from the property,

Do the following:

In the form load event where the control is located, change the connection string of the control:

Adoc1.connectionstring="What ever your connection string is" & App.path & "\DatabaseName.mdb"

Then, you must refresh the control for the changes to take effect

Adoc1.refresh

Hopefully you be able to connect that way.

Since the datagrid's datasource is bound to that control, you should now see all of your data.

You should be able to create a new recordset that will allow you to add, update and delete etc.

mgiambrone43,

If I used that connection string, wouldn't my database have to be in the same directory as my application? The application needs to be installed on at least two computers which access the same database. Using that connection string, would I have to install the application on the server instead?

Sorry if I sound like such a newbie, but I've never written a networked application like this before. This program is for my senior project and none of my team members have much networking experience.

Thanks for your help!

I think I fixed the problem, so here's how I did it for anyone who with the same problem who stumbles across this thread... I simply removed the line that says:

conResult.Provider = "Microsoft.Jet.OLEDB.4.0"

I imagine that's because ODBC takes care of what type of database it is. When I specified the provider, the ADODC was probably looking for an Access database, but when it found an DSN instead it didn't know what to do. At least that's my guess :)