Read only message for DAT files, works with TXT files

[Darin]

I have the following code that opens a comma-delimited text file:

connS = "Provider=Microsoft.Jet.OLEDB.4.0;"
connS &= "Data Source=\tmp;"
connS &= "Extended Properties=""text;HDR=No;FMT=Delimited"""
Dim tmpConnection As New System.Data.OleDb.OleDbConnection(connS)
tmpConnection.Open()

strsql = "SELECT * FROM PAYROLL.TXT"
Dim da As New System.Data.OleDb.OleDbDataAdapter(strsql, tmpConnection)
Dim ds As New DataSet("Workbook")
Dim dt As DataTable
Dim drRow As DataRow

da.Fill(ds, "Sheet1")
dt = ds.Tables("Sheet1")

When the file name is PAYROLL.TXT, this works perfectly. When the file
name is PAYROLL.DAT, I receive an error:

Cannot update: Database or object is read-only

on the line da.fill.
Why?

Darin

 

[Jeff Johnson [MVP: VB]]

I have the following code that opens a comma-delimited text file:

connS = "Provider=Microsoft.Jet.OLEDB.4.0;"
connS &= "Data Source=\tmp;"
connS &= "Extended Properties=""text;HDR=No;FMT=Delimited"""
Dim tmpConnection As New System.Data.OleDb.OleDbConnection(connS)
tmpConnection.Open()

strsql = "SELECT * FROM PAYROLL.TXT"
Dim da As New System.Data.OleDb.OleDbDataAdapter(strsql, tmpConnection)
Dim ds As New DataSet("Workbook")
Dim dt As DataTable
Dim drRow As DataRow

da.Fill(ds, "Sheet1")
dt = ds.Tables("Sheet1")

When the file name is PAYROLL.TXT, this works perfectly. When the file
name is PAYROLL.DAT, I receive an error:

Cannot update: Database or object is read-only

on the line da.fill.
Why?

More than likely a quirk of the OLE DB engine. I know that in Access you
can't import a text file unless it ends .txt, .csv, or .asc. Probably
something similar is happening here. You might ask in a group dedicated to
data and/or Access.

 

[Cablewizard]

Yes, this is a really annoying quirk of the OLEDB engine.
There are many cases where everything appears to work fine, as long as you use
certain file extensions. Change it, and it no longer works. You can explicitly
set everything up and tell OLEDB exactly what you are doing, but it will still
do its own thing.
Sometimes you can have everything right, and it still tries to do what it thinks
is best. This is very common with trying to read Excel files. For example, you
will try everything to make it treat a column as Text, but if enough fields
"look" like numbers, it will treat them as numbers. Even if it is prop'd as Text
in the .XLS.
Sometimes spaces and/or the length of the file path won't work. Sometimes it
does.
Sometimes the only fix is to drill into various deep and obscure portions of the
registry.

Fortunately, most of these quirks only exist in the Jet portion, but certainly
not always.
In the end, your best bet is to work around these quirks as trying to circumvent
them is oftentimes unsuccessful. Or not use OLEDB, but that isn't usually the
best choice.
OLEDB works great, as long as you work the way it wants you to.

Gerald