ERROR [HY000] General error: Invalid file dsn ''

Question

I am trying to use this code snippet And I get the following error message:

System.Data.Odbc.OdbcException (0x80131937): ERROR [HY000] General error: Invalid file dsn ''
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
   at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection o
wningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldC
onnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnecti
onOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.Odbc.OdbcConnection.Open()

The code, stripped down is:

open System
open System.Data.Odbc

let connectToAccess filename =
    let connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;FileDSN=" + filename + ";User Id=admin;Password=;"
    new OdbcConnection(connectionString)

let connectToDb() =
    let accessFile = Environment.CurrentDirectory + @"\folder\filename.accdb"
    connectToAccess accessFile

let connection = connectToDb()
connection.Open()

I did check and verify that the path and filename are correct. I have also tried:

• relative paths
• a dummy access file (to make sure that the file is not corrupted)
• an older *.msb file
• run the code in the terminal
• run the code as an executable - ensuring that the path is still ok

Answer 1

You're confusing the DSN to an accdb file with an accdb file.

A FileDSN contains details on how to connect to the database, but you're passing a path to the database instead of a path to a file with details on how to connect to the database.

In addition, you're using Microsoft.Jet.OLEDB.4.0 as the provider, but trying to use an ODBC connection, not an OLEDB connection and thus need an ODBC driver not an OLEDB provider, and trying to use an accdb file, while Jet only supports mdb files.

Use a standard connection string. ConnectionStrings.com is a popular resource, but the docs are also filled with them.

Example (for an accdb file, requires the Access Database Engine to be installed and matching bitness with your program).

let connectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" + filename + ";"

Note that these errors are all present in the code snippet. That snippet never worked.