I have a VB.NET app with an ADODB/OLEDB Connection to MSSQL Server. I open a ForwardOnly recordset and it is working fine.

I need to temporarily change the connection to Access (mdb) until I get the server database fully populated. However when I change the Connection String the recordset becomes read only.

Permissions to the Access database shouldn't be the issue as it has no security and the folder permissions are correct. The Access database is currently also working fine as a read write backend.

Here is the change I am making to the connection sub.


Sub ConnectTransactions()

TransactionData = CreateObject("ADODB.Connection")
With TransactionData
'.ConnectionString = "Provider=SQLNCLI;Data Source=" & SERVER & ";Database=TransactionData;Trusted_Connection=Yes"
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\SERVER\SHARE\Transdata.mdb"
.ConnectionTimeout = 20
.Mode = adModeReadWrite
EndWith

EndSub

Worked it out. The penny dropped when I tried an ODBC connection and got the "Operation must use and updateable query" error message.

There is no Primary Key on the Access table. It hadn't needed one when it was being populated with a Insert query using linked tables in the front end.