Error hy000 microsoft odbc microsoft access

I am running on Windows 7, Python 2.7 and Microsoft Access 2013.

When I try running:

import pyodbc
conn_string = '''
DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};
UID=admin;
UserCommitSync=Yes;
Threads=3;
SafeTransactions=0;
PageTimeout=5;
MaxScanRows=8;
MaxBufferSize=2048;
FIL=MS Access;
DriverId=25;
DefaultDir=C:Usersjseinfeld;
DBQ=C:UsersjseinfeldDesktopDatabasetest1.accdb;
'''
connection = pyodbc.connect(conn_string)

I receive the following error message:

Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x29dc Thread 0x113c DBC 0x8a3ed48    

Jet'. (63) (SQLDriverConnect); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x29dc Thread 0x113c DBC 0x8a3ed48

Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Not a valid file name. (-1044); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x29dc Thread 0x113c DBC 0x8a3ed48 

Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x29dc Thread 0x113c DBC 0x8a3ed48

Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Not a valid file name. (-1044)")

I realize there are many questions regarding Pyodbc and MS Access. I have already executed the following:

1) Ensured I have 64 bit Python and 64 bit MS Access

2) Given permission to the account access to the HKEY_LOCAL_MACHINESOFTWAREODBC registry key

https://support.microsoft.com/en-us/kb/295297

Error in opening an Access database in python

«General error Unable to open registry key Temporary (volatile) …» from Access ODBC

3) Tried to ensure I have a valid connection string https://stackoverflow.com/questions/6469545/python-connecting-to-a-database-with-pyodbc-not-working#=

4) The Access database is not open when I try running this code. I have re-booted my computer and am still receiving this error.

Please let me know if there is any other action I can try.

Hello,
For my own needs I started writing a pyodbclib, that contains connection 
strings to make it easier for the users. Anyway, this code works fine for me:

import pyodbc

def access2007(db, sqlstring='select * from table', user= 'admin', password=""):
    """Create function for connecting to Microsoft Access using ODBC database connection."""
    odbc_conn_str = 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=%s;Uid=%s;Pwd=%s;' % (db, user, password)
    conn = pyodbc.connect(odbc_conn_str)
    cur = conn.cursor()
    cur.execute(sqlstring)
    data = list(cur)
    conn.close()
    return data

def excel2007(db, sqlstring='select * from table', user= 'admin', password=""):
    """SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]".
    I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.
    """
    odbc_conn_str = 'Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=%s;' % (db)
    conn = pyodbc.connect("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; Dbq="+db, autocommit=True)
    cur = conn.cursor()
    cur.execute(sqlstring)
    data = list(cur)
    conn.close()
    return data

if __name__ == '__main__':
    """Now I can access Excel, Access, etc. using a simple function call:
    >>> import pyodbc_connection as pc
    >>> db = r'C:pyodbc_access2007_sample.accdb'
    >>> sql="select * from [Customer Orders]" 
    >>> data = ps.access2007(db,sql)
    >>> print len(data)
    129606
    """

    # Access DB example
    db1 = r'C:UsersbmadsenownCloudPythonNoteBookpyodbc_access2007_sample.accdb'
    sql1="select * from [Customer Orders]" ## tables: 'Customer Orders', 'Physical Stoks','Prodplans'
    data1 = access2007(db1,sql1)
    print len(data1)

    # Excel example
    db2 = r'C:UsersbmadsenownCloudPythonNoteBookpyodbc_excel2007_example.xlsx'
    sql2='SELECT * FROM [Sheet1$]'
    data2 = excel2007(db2,sql2)
    print len(data2)

Error hy000 microsoft odbc microsoft access

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

Hi, I have a report that pulls data from an access97 db via an ODBC DSN connection. Everything works fine on my workstation but when I try to implement on prod server I get the following error.

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not use ‘(unknown)’; file already in use.

I have configured the ODBC DSN to use the same mdw file.

any help apreciated

Access is a file based RDBMS — as opposed to a server based RDBMS like sql server (or Oracle). You can set up an ODBC dsn on a local machine it appears, but it will not work across a network — as you are experiencing. You need a server based RDBMS for that.

Hi, I have a report that pulls data from an access97 db via an ODBC DSN connection. Everything works fine on my workstation but when I try to implement on prod server I get the following error.

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not use ‘(unknown)’; file already in use.

I have configured the ODBC DSN to use the same mdw file.

Источник

Error hy000 microsoft odbc microsoft access

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

Hi, I have a report that pulls data from an access97 db via an ODBC DSN connection. Everything works fine on my workstation but when I try to implement on prod server I get the following error.

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not use ‘(unknown)’; file already in use.

I have configured the ODBC DSN to use the same mdw file.

any help apreciated

Access is a file based RDBMS — as opposed to a server based RDBMS like sql server (or Oracle). You can set up an ODBC dsn on a local machine it appears, but it will not work across a network — as you are experiencing. You need a server based RDBMS for that.

Hi, I have a report that pulls data from an access97 db via an ODBC DSN connection. Everything works fine on my workstation but when I try to implement on prod server I get the following error.

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not use ‘(unknown)’; file already in use.

I have configured the ODBC DSN to use the same mdw file.

Источник

Error hy000 microsoft odbc microsoft access

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

The following (VB.Net) code causes exceptions at seemingly random times.
Any suggestions?
Not sure if the problem is in .Net’s ODBC support or in Sql Native Client.

If MARS is off, usually after less than 100 loops:

System.Data.Odbc.OdbcException was unhandled
ErrorCode=-2146232009
Message=»ERROR [HY000] [Microsoft][SQL Native Client]Connection is busy with results for another command»
Source=»SQLNCLI.DLL»

System.Data.Odbc.OdbcException was unhandled
ErrorCode=-2146232009
Message=»ERROR [23000] [Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL into column ‘Number’, table ‘tempdb.dbo.#TempTable___..(shortened to fit).. _________000000002F3F’; column does not allow nulls. INSERT fails.
Source=»SQLNCLI.DLL»

Code here:
(Console Application, empty database,
Visual Studio 2005, .Net 2.0, Windows XP,
SQL Server 2005 or SQL Express 2005, Local or Remote)

‘Dim connection As New SqlClient.SqlConnection(«database=TestDB;server=.sqlexpress;Integrated Security=SSPI;») ‘Doesn’t crash
‘Dim connection As New OleDb.OleDbConnection(«Provider=SQLNCLI;database=TestDB;Server=.sqlexpress;Trusted_Connection=yes;») ‘Doesn’t crash

‘Dim connection As New Odbc.OdbcConnection(«Driver=;Database=TestDB;Server=.sqlexpress;Trusted_Connection=yes;») ‘Crashes
Dim connection As New Odbc.OdbcConnection(«Driver=;Database=TestDB;Server=.sqlexpress;Trusted_Connection=yes;MARS_Connection=yes») ‘Crashes

Dim run_count As Integer = 1
connection.Open()
Console.WriteLine(«Connected!!»)

While True
‘connection.Open()

Dim testcmd As New Odbc.OdbcCommand(«CREATE TABLE [#TempTable] (Number int PRIMARY KEY)», connection)
testcmd.ExecuteNonQuery()
testcmd.Dispose()
testcmd = Nothing

Dim dtTemp As New DataTable
Dim daTemp As New Odbc.OdbcDataAdapter(«SELECT * FROM [#TempTable]», connection)
daTemp.MissingSchemaAction = MissingSchemaAction.AddWithKey
daTemp.Fill(dtTemp)
Dim cbTemp As New Odbc.OdbcCommandBuilder(daTemp)
Dim viewTemp As New DataView(dtTemp, Nothing, «Number», DataViewRowState.CurrentRows)

Dim i As Integer
For i = 1 To 1000
Dim test_number As Integer = CInt(Rnd() * 2000)
If viewTemp.Find(test_number) = -1 Then ‘Keep it unique
Dim new_temp_row As DataRowView = viewTemp.AddNew()
new_temp_row(«Number») = test_number
new_temp_row.EndEdit()
End If
Next

‘daTemp.Dispose()
‘daTemp = Nothing

‘cbTemp.Dispose()
‘cbTemp = Nothing

‘dtTemp.Dispose()
‘dtTemp = Nothing

‘viewTemp.Dispose()
‘viewTemp = Nothing

Dim testcmd2 As New Odbc.OdbcCommand(«DROP TABLE [#TempTable]», connection)
testcmd2.ExecuteNonQuery()
testcmd2.Dispose()
testcmd2 = Nothing

‘Console.Write(«.»)
run_count += 1
End While

Usually the «.Update» triggers the exception, but sometimes the other sql commands do it.
Have tried various experiments with disposing of objects immediately when finished, but no effect.
Opening and closing the connection each loop seems to delay the errors, but it still happens eventually.

Источник

Error hy000 microsoft odbc microsoft access

Well, let’s take one issue at a time, shall we?

If you using the x64 bit version, and you talking about an accdb file to migrate?

Then MAKE SURE you download and install the x64 bit version.

There are two versions:

SSMAforAccess_8.9.0.msi (this is for x64 bit access)

SSMAforAccess_8.9.0._x86 (this is for x32 bit access)

Ok, assuming you installed the correct version (your case x64 bits).

Next up, you have to install a working copy of the ACE database engine. In the past, JUST installing Access or HAVING access installed would work. However, since about access 2013, installing Access does NOT expose the ACE data engine for use with OTHER 3 rd party tools.

So, if you going to use VB6, vb.net (c#), c++, FoxPro or even SSMA? You have to STILL install a working copy of the ACE data engine that is exposed for you use.

You find a copy here:

Again, there are two versions – make sure you grab + install the x64 bit version.

Once you do this, then SSMA should work fine.

And the same quite much applies to any other 3 rd party system. (such as Python).

So, I would get SSMA working, but the above steps apply to any other 3 rd party system, ranaging from .net, web servers, or in your case Python.

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada

Thanks for your attention.

I install the runtime and I started to get this error.

Refreshing objects from database.
Analyzing metadata.
Preparing mdb-file BaseGaia_be.
Starting Phase #0
Processing database ‘BaseGaia_be’. .
Refreshing the database BaseGaia_be.
Access Object Collector error: Database
The database you are trying to open requires a newer version of Microsoft Access.
An error occurred while loading database information.
Access Object Collector error: Database
The database you are trying to open requires a newer version of Microsoft Access.
An error occurred while loading database content.
Refresh database operation complete.
Refresh operation is complete.

Ok, remember that you have to launch the correct version of SSMA. (32 or 64)

and you ALSO have to install the ACE engine.

Installing Access full edition, or even runtime will NOT work!

You HAVE to install ACE. (x64 bit version).

So installing Access runtime will not fix or make this work. You need to install ACE.

Sorry, what you want to mean about ACE engine? Is it Access Software?

I Have installed Office 365 with Access and I install the Microsoft Access Database Engine 2016 Redistributable from the link that you sent to me. All 64bits

If you installed the ACE redistributable as you say, then your setup should work.

Just make sure that SSMA is also x64 bits. (you can have both versions — so make sure you launch the right version.

I would also check if you can open the accDB with Access. Do a compact + repair, and then exit, and try SSMA again.

Thanks for your help but unfortunately nothing works 🙁

Nothing works is not very descriptive here.

You are obviously missing some steps here, or are ignoring error messages during the install.

There is quite a good outline here as to using SSMA.

This should be rather easy. It not clear if you sticking x32 bit versions of office, or that of x64 bits.

Given this seems to be a rather “old” version of data (mdb), which dates back to 2003 that is a WHOPPING 17 years ago.

As a result? I would stick to x32 bits until you migrated out of that mdb file (to say sql server).

And once that data migrated to SQL server, then you have much more ease using python to work with the data now that it is on sql server.

And of course attempting migration to SQL server, then I would of course say create a simple test table in a accDB file with access, and then try sending that to sql server.

You not really outlined what you attempted here.

I would however suggest using x32 bit software to do the migration to sql server, and this is due to you using a 17 years old “mdb” file.

So, I would try using all x32 bit software here. (sql server can be x64 or x32 — it DOES not matter which version).

So, Install ACE data engine 2016 redistribute – x32

Then installing SSMA x32 version. Launch SSMA – it works just fine for me. You don’t even need or to have access having been installed.

And is the just the one mdb file that does not migrate?

How about spending 2 minutes of time and trying an accDB file — does that work?

And does SSMS open the access file in question? Try another access file – does that work?

I would also consider attempting this on a different computer – it seems that your current computer is a “mess”, and not much of anything is working correctly.

While writing this post, I just spooled up a fresh copy of win 10 in a virtual machine.

I installed ACE x32 and SSMA on a fresh copy of windows 10. It is interesting, but before I could install SSMA, it warned and asked me to install .net 4.72. But, being a fresh new copy of windows — that is expected.

So, I did not even install access at all. It works fine, and was really less than 10 minutes of efforts here.

So, ACE 2016 re-dist install (x32)

Take the x86 verion (x32 bits). You have two choices from above when you download.

Try the x86 (32) bit version

And then install SSMA 8.1 from here:

Again, take/use the x32 bit version.

This is two simple installs, and you should be off to the races.

And you not noted which versions of SSMA and the re-dist you attempted here?

So, without sharing what versions you attempted, then we shooting in the dark.

Take both the above.

So, ACE 2016 — saves you having to install access – this is quicker and lighter.

Make sure you use the “later” versions of SSMA, since just a few versions ago, they were forcing x64 bit installs, and that caused a lot of grief.

Both above tools are free, and you don’t need to have access, or even install Access with the above.

So, it not clear if your “installs” are completing or going smooth?

I would however try using all x32 bit software. And even today, 99% of office installs are x32, even those running on x64 bit boxes that today run x64 bit OS, and have been that way for 10+ years now.

In the time to type this out, my second screen was able to install both of the above, and I am able to open both mdb, and accDB files with SSMA without issues.

My spider sense suggests that your computer is a mess — I would try this on another computer.

Источник