Run time error 3151 odbc ошибка подключения к - Не ошибается лишь тот, кто ничего не делает!

We have an Access database migrated from Access 97 to Acces 2007 with some linked tables to an SQL Server 2008 database.
We are using a File DSN in the ODBC Administrator. Connection tests work fine there. In fact, we have a main form in the Access 2007 application that shows the data always correctly.

The problem appears when we execute a task that makes the following call:

Dim dbs As Database Dim rstAppend As Recordset Set dbs = CurrentDb Set rstAppend = dbs.OpenRecordset(strAccessTable, dbOpenDynaset, dbSeeChanges)

OpenRecordset throws the error number 3151 and occurs randomly but it appears quite frequently. It’s sporadic. When we don’t get the error, the task works fine.

It not happens always, mostly we’re fine.
When it happens it happens for all subsequent tasks tried. We have to close and reopen the Access database and pray to the good Lord that it works.
Sometimes, refreshing the tables in the Linked Table Manager solves the problem, but others don’t.
We receive sometimes an error «ODBC—Call Failed» when refreshing the Linked Table Manager. Deleting the File DSN and creating a new one solves the problem.
We have tried with two different drivers (SQL Server and SQL Server Native Client 10) and in both cases the problem remains.
Also we have tried to put the Access File in the same machine than the SQL Server and the problem remains.
We have increased the ODBC Timeout for all the querys from 60 to 180 seconds,but the problem still remains.
We don’t have to wait to see the error, it appears in less than one second after executing the task.

We would be very happy if someone could help us to find a solution to this problem.

Tony Toews

7,8401 gold badge22 silver badges27 bronze badges

asked Sep 24, 2010 at 13:21

After two months of research, we found one solution, change from DAO to ADO. This is the answer MS gave me:

I’d like to make you aware that implementing the workaround (using ADO instead of DAO) could be less time consuming than trying to figure out why DAO doesn’t seem to work.
Therefore it might be advisable using the workaround instead of trying to find the root cause of this issue, especially as it doesn’t seem to be reproducible.

However; here is what I’d like to you do next:

Please replace the file DSN by a system or User DSN.
Use the ‘normal’ ODBC driver for SQL- Server instead of the native driver.
After creating the user DSN re link all the tables.
In VBA code use explicit type declarations only instead of implicit declarations. So please replace use DAO. In front of any database type declarations in order to explicitly create DAO objects. For example replace the following:

Dim dbs As Database
Dim rstAppend As Recordset
By
Dim dbs As DAO.Database
Dim rstAppend As DAO.Recordset

answered Dec 2, 2010 at 15:31

rodpedjarodpedja

511 gold badge1 silver badge6 bronze badges

Switching from TCP/IP to Named Pipes in the Client Configuration for the SQL Server ODBC Driver did the trick for me.

answered May 24, 2012 at 17:56

One thing to look at is the DNS setup (DNS not DSN!) in the network configuration for the workstations.

I had a client a couple of years ago who was encountering sporadic ODBC disconnects like this with some users, and it turned out that the primary DNS was set to point to the Internet provider’s DNS. That worked fine for Internet, but the ISP knew nothing about the client’s SQL Server’s internal IP address. Changing the primary DNS to point to the local domain controller (which was acting as local DNS) resolved the issue permanently.

It may not be the cause of your problem, but it’s worth a look.

answered Sep 25, 2010 at 20:44

David-W-FentonDavid-W-Fenton

22.8k4 gold badges45 silver badges58 bronze badges

I was having this issue as well. What i did was added the DSN to the SQL database in the User tab. I noticed it was working on my dev system, and that’s the only difference i could find. After creating the DSN on the users PC, it worked.

answered Aug 29, 2018 at 0:52

Источник

Summary:
You may receive “Access Database 3151 Error – ODBC Connection Failed” when using ODBC protocol in Access. It can occur due to numerous reasons. In this blog, we’ll discuss causes of the error and the possible solutions to fix it. It also mentions an Access repair software that can help fix the “ODBC connection failed” error if it occurs due to corruption in database.

Contents

Causes of Access Database 3151 Error – ODBC Connection Failed
Methods to Fix Access Database 3151 Error – ODBC Connection Failed
Closure

The Access Database Error 3151 – ODBC connection failed usually occurs when using a connection string (ODBC) in a VBA code. Open Database Connectivity (ODBC) is a protocol that is used to connect a Microsoft Access database to an external data source, such as Microsoft SQL Server. This error may also occur after performing some changes in the SQL Server and in linked tables in the database. However, there are various other reasons that may cause this error. Let’s take a look at the possible causes of this error and the solutions to resolve it.

Causes of Access Database 3151 Error – ODBC Connection Failed

Following are some possible reasons that can trigger the ODBC connection failed error in MS Access:

Incorrect SQL Server Authentication
ODBC driver for SQL Server is not properly installed
Network connection issue
Corruption in the Access database
Named pipe settings in Windows are disabled
Incorrect Tracing Settings
Windows Firewall is not allowing the connection
Data source name is not defined to pass the connection information directly to the ODBC Driver Manager

Methods to Fix Access Database 3151 Error – ODBC Connection Failed

Below are some methods you can try to resolve the error 3151 – ODBC connection failed in MS Access.

Method 1: Check the ODBC Credentials

The first thing to do is to check the ODBC credentials. The Access database 3151 error can occur if you are using incorrect ODBC credentials. You can confirm the Login ID and Password set at the time of ODBC connection creation. Follow the below steps:

Open your Access database.
Go to the File option, click External Data, and then click ODBC Database.

In the Get External Data – ODBC Database window, select “Link to the data source by creating a linked table.”

In the Select Data Source dialog box, click on the Machine Data Source option and then click New.
In the Create New Data Source window, select SQL Server and then click Next.

Now, click Finish.

The Create a New Data Source to SQL Server window is displayed. Check the login credentials and then click Next.

Check if the error 3151 is resolved. If not, then follow the next methods.

Method 2: Enable Named Pipe Setting

“Named Pipe” is a protocol used to establish connection between network users and Microsoft SQL Server. The ODBC connection may fail if this protocol is disabled. Follow the below steps to enable Named Pipe setting:

Go to your System’s Control Panel.
On the All Control Panel Items page, click on Windows Administrative Tools.

Double-click Local Security Policy.

In Local Security Policy, click Security Settings.

Click Local policies > Security Options and then right-click on “Network access: Named Pipes that can be accessed anonymously” and then select Enabled.

Method 3: Enable Tracing Options

ODBC Tracing allows you to create a log of the traces. The error 3151 in MS Access can occur if there is an issue with the tracing capabilities of the ODBC Administrator. You can configure the Tracing tab of the ODBC Administrator using the below steps:

Login into the Local Administrators group.
From Administrative Tools, click ODBC Data Source Administrator.
Select the Tracing tab.
Now, select the “Machine-Wide Tracing for all identifies” checkbox to configure the tracing mode.
Choose the checkbox to enable machine-wide tracing.
Clear the checkbox to return to per-user tracing.
Click Apply.

Method 4: Verify the TCP Connection

Sometimes, the ‘ODBC connection failed’ error can occur due to incorrect TCP ports or IP addresses. Ports related to database engine services are TCP 1433, 4022, 135, 1434, and UDP 1434. You can check the active TCP connection and IP Statistics using the below steps:

Open the Command Prompt window (press Windows + R, type cmd, and click OK).
In Command Prompt, enter netstat -n –a.

It will display the information about TCP, IP address, port numbers, connection and listening ports, and more.

Method 5: Check the ODBC Connection Database Setup

To use the ODBC (Open Database Connectivity) protocol, you must do the following:

Install the appropriate ODBC driver on the system that contains the data source.
Define the DSN by using either the ODBC Data Source Administrator or a connection string in a VBA to pass the connection information directly to the ODBC Driver Manager.

If there is an issue with ODBC Driver, you may encounter the ODBC connection failed error. So, check and install the correct ODBC driver for the data source you need to connect.

Method 6: Repair your Access Database

Sometimes, the Access database 3151 error can occur due to corruption in the database. In such a case, you can try repairing your database using Access’ built-in utility – Compact & Repair. Follow these steps:

Open your database.
Go to File > Info > Compact & Repair Database.

A copy of the compacted and repaired database is created at the same location.

If the “Compact and Repair” tool fails to fix the corruption in the Access file, then you can use an advanced Access repair tool, such as Stellar Repair for Access. This tool can help you repair severely corrupted MDB/ACCDB files. It can recover all the database objects, including tables, linked tables, queries, forms, reports, indexes, macros, modules, etc. Also, the tool helps you preview the recoverable database objects before saving.

Closure

When using an ODBC connection in Microsoft Access, you may encounter the “Access database 3151 error – ODBC Connection Failed”. It can occur due to several reasons. You can try the solutions discussed above to fix the error. Sometimes, the error can occur if your database is corrupt. In such a case, you can use Stellar Repair for Access. It is a professional tool that can easily repair severely corrupted Access database files and help fix errors. The software supports Office 365, Access 2019, 2016, 2013, and lower versions.

About The Author

Monika Dadool

Monika Dadool is a Technical content writer at Stellar who writes about QuickBooks, Sage50, MySQL Database, Active Directory, e-mail recovery, Microsoft365, Pattern Recognition, and Machine learning. She loves researching, exploring new technology, and Developing engaging technical blogs that help organizations or Database Administrators fix multiple issues. When she isn’t creating content, she is busy on social media platforms, watching web series, reading books, and searching for food recipes.

Источник

Synopsis: Let us discuss the ways to troubleshoot Access Runtime Error 3151 also known as ODBC called failed error 3151.

This error occurs in MS Access when the user makes the same ODBC connection with MS Access as MS SQL Server.

Some of the symptoms that you might experience in Access Runtime Error 3151 are:

The active program window crashes.
Your PC frequently crashes while running the same program.
Display Error message-“Microsoft Access Error 3151”.
Slow Windows Response.
Slow response to mouse or keyboard input.
Sudden computer freeze for a few seconds.

Note: You need to keep a track of the time of occurrence of Access Runtime error 3151 like during installation of MS Access or Windows, during windows startup and shutdown. As this will make it easier to fix or troubleshoot Access runtime error 3151.

Causes of Access Runtime Error 3151

Some of the common causes of Access Runtime Error 3151 are:

Incomplete and Improper installation of MS Access software.
Corruption caused in Windows registry from a recent Microsoft Access-related software change (install or uninstall).
Virus or malware infection that has corrupted Windows system files or Microsoft Access-related program files.
Another program maliciously or mistakenly deleted Microsoft Access-related files.

Ways to troubleshoot MS Access Runtime Error 3151

Just follow the step by step procedure and fix the problem. Let us see what the first method says:-

1. Method to manually resolve Access Error 3151

Go to the File Tab on the top-left corner and then Click on it.
Now Click on Get External Data option and then click on Link Tables.
From the File Type List Select ODBC Database and then click on Machine Data Source option.
Make a Click on New and Select System Data Source and Click on Next button to continue.
Enter the ODBC Connection name and the SQL Server name. Click on Next to continue.
Now, choose the desired database and click on Next and then on Finish.
Check your ODBC Connection to SQL Server.

Click on OK button again and again, until SQL Server Log in Page appears.
Enter the credentials and login details and then click on OK.

2. Virus Scan or Malware scan

This error is displayed when the MS Access files are corrupted. Virus or Malware attack can also cause corrupt files. So, you need to scan for virus and malware with a good antivirus.

3.Use Window System Restore

With this option, you can restore the files back to the state when they were healthy. This will also not affect your data and other files.

4.Install updates related to Windows

For this go to start, button>enter “update” in search box>Windows update dialog box will be displayed> Click on “Install Updates” if updates are present.

5. Uninstall and Reinstall MS Access Program

By doing this you can easily troubleshoot Access Runtime Error 3151.

If these methods are not helpful to you, try using the next method.

6.Professional MS Access Database Recovery Tool

The professional MS Access Database Recovery Tool is designed to repair severely corrupted MDB files which are the main cause of any error in Access Database and therefore it fixes alignment errors and restores the MDB files in the original form.

Conclusion

In this post, I have discussed few methods to troubleshoot Access Runtime Error 3151. The issue can be resolved with the help of manual methods, But,if the error is still unresolved,Then you can use the Professional Access Database Recovery Tool,

Источник

-55 / 4 / 0

Регистрация: 10.09.2016

Сообщений: 365

14.12.2021, 17:56. Показов 520. Ответов 3

Здравствуйте.

Работаю в Microsoft Access 2016, Windows 7 64 bit.
Пытаюсь открыть БД (созданнёю не мной) и получаю ошибку Run-time error ‘3151’: «ODBC-connection to … failed.»
По материалам многочисленных источников пытаюсь настроить правильное соединение: External Date -> New Data Source -> From Other Sources -> ODBCDatabase-> Link to the data sources by creating a linked table -> Machine Data Source -> New

Получаю сообщение: You are logged on with non-Administrative privileges. System DSNs could not created or modified

~~Eugene-LS~~

Заблокирован

14.12.2021, 18:18

Сообщение от vlanatvaszla

Получаю сообщение

Я делал так:

Установка драйвера ODBC MySQL:
01. На страницу: http://dev.mysql.com/downloads/connector/odbc
(Проще скачивать с: https://mirror.yandex.ru/mirro… ctor-ODBC/)
02. Находите желаемую версию и скачиваете установочный пакет (Например: mysql-connector-odbc-8.0.22-winx64.msi).
03. Запускаете программу установки, все настройки по умолчанию.
04. После установки можно сразу работать.

Модуль:

Кликните здесь для просмотра всего текста

Visual Basic

'--------------------------------------------------------------------
' Module    : modLinkTablesMySQL
' Author    : es
' Date      : 21.08.2016
' Purpose   : Подлинковка таблиц MySQL с автоматическим созданием DSN (ADOX или DAO)
'--------------------------------------------------------------------------
'Для ADOX - Требуются ссылки на:
'    Microsoft ADO Ext. for DDL and Security. (Version 2.1 or higher)
'    Microsoft ActiveX Data Objects Library. (Version 2.1 or higher)
'--------------------------------------------------------------------
 
Public Sub LinkTablesMySQL_ADOX()
'Создание общей строки подключения и подлинковка таблиц MySQL
'--------------------------------------------------------------------------
Dim sDriver As String
Dim sServerAdr As String
Dim sServPort As String
Dim sDbName As String
Dim sUser As String
Dim sPassWord As String
Dim sConnect As String
Dim l&
'--------------------------------------------------------------------------
On Error GoTo LinkTablesMySQL_ADOX_Err
'Внимание - проверь версию драйвера! - Может уже... 5.5 или даже 6.2!?
 
    sDriver = "{MySQL ODBC 8.8 Unicode Driver}"  'UNICODE Driver
    
    'sServerAdr = "localhost"
    sServerAdr = "127.0.0.1"   ' Адрес (Имя) сервера
    sServPort = "3306"         ' Порт соединения = 3306 (Обычно так и есть)
    sDbName = "codelibrary_db" ' Название базы
    sUser = "root"             ' Имя пользователя
    sPassWord = "Pass_Word"    ' Пароль
 
 
'Создаю строку подключения:
    sConnect = "ODBC;DRIVER=" & sDriver & _
        ";SERVER=" & sServerAdr & _
        ";Port=" & sServPort & _
        ";DATABASE=" & sDbName & _
        ";USER=" & sUser & _
        ";PASSWORD=" & sPassWord & _
        ";OPTION=3" 
        '";OPTION=4194304" & _
        ;ConnectionLifeTime=300; & _
        ";stmt=set names cp1251"
        '";stmt=set names utf8"
   'Debug.Print sConnect
 
 
    DoCmd.Hourglass True 'Показать часики
 
'--------------------------------------------------------------------
'Подключаем несколько таблиц по ADOX
    l = LinkTable_ADOX("table_name_01", sConnect)
    If l > 0 Then Err.Raise l
    
    l = LinkTable_ADOX("table_name_01", sConnect)
    If l > 0 Then Err.Raise l
        
    l = LinkTable_ADOX("table_name_03", sConnect)
    If l > 0 Then Err.Raise l
   
'--------------------------------------------------------------------------
'Подключаем пару таблиц по DAO
    l = LinkTable_DAO("table_name_04", sConnect, "table_name_04_DAO")
    If l > 0 Then Err.Raise l
    
    l = LinkTable_DAO("table_name_05", sConnect, "table_name_05_DAO")
    If l > 0 Then Err.Raise l
 
'Обновление Области Навигации (Navigation Pane)
    CurrentDb.TableDefs.Refresh
    Application.RefreshDatabaseWindow
    DoEvents
 
    Debug.Print "--------------------------------------------------"
    Debug.Print "Подключено: " & Now
    Debug.Print "--------------------------------------------------"
 
        
LinkTablesMySQL_ADOX_Bye:
    DoCmd.Hourglass False 'Вернуть нормальный курсор
    Exit Sub
 
LinkTablesMySQL_ADOX_Err:
    MsgBox "Ошибка " & Err.Number & vbCrLf & Err.Description & vbCrLf & "в процедуре: LinkTablesMySQL_ADOX", vbCritical, "Error in module modConnectMySQL_DAO"
    Resume LinkTablesMySQL_ADOX_Bye
End Sub
 
 
Private Function LinkTable_ADOX(stRemTName As String, strConnect As String, Optional sLocalTName As String = "") As Long
'es 21.08.2016
'Подлинковка таблички MySQL Server с автоматическим созданием DSN (ADOX)
'При удачном подключениии возвращает = 0 (ноль), при неудачном = КОД ОШИБКИ (номер)
'-------------------------------------------------------------------------
'Аргументы:
'   stRemTName  = Имя таблицы на сервере
'   strConnect  = Строка подключения к серверу
'   sLocalTName = Локальное Имя Таблицы
'-------------------------------------------------------------------------
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
    
On Err GoTo LinkTable_ADOX_Err
 
'Если локальное имя не указанно
    If sLocalTName = "" Then sLocalTName = stRemTName
    
'Открываем каталог текущей базы
    Set cat.ActiveConnection = CurrentProject.Connection
    
'Если таблица с таким названием уже существует - Удаляем
    For Each tbl In cat.Tables
        If tbl.Name = sLocalTName Then cat.Tables.Delete tbl.Name
    Next
    
'Установка параметров таблицы
    With tbl
        .Name = sLocalTName
        Set .ParentCatalog = cat
        .Properties("Jet OLEDB:Link Provider String") = strConnect
        .Properties("Jet OLEDB:Remote Table Name") = stRemTName
        .Properties("Jet OLEDB:Create Link") = True
    End With
    
'Создаём новый обьект
    cat.Tables.Append tbl
 
'Обновляем список таблиц
    cat.Tables.Refresh
    CurrentDb.TableDefs.Refresh
    DoEvents
 
LinkTable_ADOX_Bye:
    Set cat = Nothing
    Set tbl = Nothing
    Exit Function
 
LinkTable_ADOX_Err:
    LinkTable_ADOX = Err.Number
    Debug.Print "--------------------------------------------------"
    Debug.Print stRemTName & vbCrLf & "Error " & Err.Number & vbCrLf & Err.Description & vbCrLf & _
            "in Function: esLinkTable_ADOX"
    Resume LinkTable_ADOX_Bye
End Function
 
 
Private Function LinkTable_DAO(sSourceTblName As String, sConnectStr As String, Optional sLocalTblName As String = "") As Long
'es - 29.12.2012
'Создаёт подлинкованную таблицу по параметрам подключения с автоматическим созданием DSN (DAO)
'При удачном подключениии возвращает = 0 (ноль), при неудачном = КОД ОШИБКИ (номер)
'--------------------------------------------------------------------
'Аргументы:
'   sSourceTblName  = Имя Таблицы Исходное (на сервере)
'   sConnectStr     = Строка подключения к серверу:
'   sLocalTblTName  = Локальное Имя Таблицы (по умолчанию = sSourceTblName)
'--------------------------------------------------------------------
Dim tdf As New DAO.TableDef
    If sLocalTblName = "" Then sLocalTblName = sSourceTblName
'Удаляем старую подлинковку (если была)
    On Error Resume Next
    CurrentDb.TableDefs.Delete sLocalTblName
    Err.Clear
   
On Error GoTo LinkTable_DAO_Err
'Установка параметров создаваемой таблицы
    With tdf
        .Name = sLocalTblName
        .Connect = sConnectStr
        .SourceTableName = sSourceTblName
    End With
    
'Создаём новый обьект Таблица
    CurrentDb.TableDefs.Append tdf
    DoEvents
    CurrentDb.TableDefs.Refresh
    
LinkTable_DAO_Bye:
    On Error Resume Next
    Set tdf = Nothing
    Exit Function
 
LinkTable_DAO_Err:
    LinkTable_DAO = Err.Number
    Debug.Print "Error " & Err.Number & vbCrLf & Err.Description & vbCrLf & _
        "in procedure LinkTable_DAO", vbCritical, "Error!"
    Resume LinkTable_DAO_Bye
End Function

Achtung!
Разрядность MySQL ODBC драйвера — должна совпадать с разрядностью MSO (а не СИСТЕМЫ).
Т.Е. Если система x64,а MSO=x32 (что довольно обычно), то — ставим драйвер — x32.

Для ADOX требуются ссылки на библиотеки:
Microsoft ADO Ext. for DDL and Security. (Version 2.1 or higher)
Microsoft ActiveX Data Objects Library. (Version 2.1 or higher)

Результат может быть таким:

Миниатюры

-55 / 4 / 0

Регистрация: 10.09.2016

Сообщений: 365

14.12.2021, 19:04

[ТС]

Драйвер ODBC MySQL я устновил. Проблема осталась.
Я получил два файла, которые возможно должны мне помочь в установке соединения. Но так ли это я не знаю.

Миниатюры

~~Eugene-LS~~

Заблокирован

14.12.2021, 19:15

Сообщение от vlanatvaszla

Я получил два файла, которые возможно должны мне помочь в установке соединения.

Один файл готовых настроек DSN, второй мне не понятен.

Видимо вам сюда:
Yandex: odbc 3151 ошибка

Источник

Remove From My Forums

Question
Hi,

I got an error after removed Access database from windows 2000 server to 2008.
My Access version are 97 on 2000 sever and 2003 on 2008server.
First, I got error that missing MSOC.dll.
Then, I could repeard that error to unchek «Office Compatible 1.0 Type LIvrary» and check in DAO check box on reference.
But, after do that, I got another error again,
«Run time error 3151; ODBC-connection to ‘name’ faild.»
On the ODBC setting test connection,
I can connect successfully.
But, still I’ve gotten same error massage.

How can I repeare ??

Thank you for your help.

If I shouldn’t ask about that quesion in this topic, so sorry.

Answers

Hi

First of all you should remove all references that you don’t need. Then you should compile the whole application. It has to compile without error.

Now you should relink all your ODBC tables and ensure that you can open them without error.

If the error still occurs try to run following statement in the VBA intermediate window (Ctrl+G) after it occured:

Msgbox Errors(0).Description

and post the result of the messagebox you get here.

Henry

Hi,

I got an error after removed Access database from windows 2000 server to 2008.
My Access version are 97 on 2000 sever and 2003 on 2008server.
First, I got error that missing MSOC.dll.
Then, I could repeard that error to unchek «Office Compatible 1.0 Type LIvrary» and check in DAO check box on reference.
But, after do that, I got another error again,
«Run time error 3151; ODBC-connection to ‘name’ faild.»
On the ODBC setting test connection,
I can connect successfully.
But, still I’ve gotten same error massage.

How can I repeare ??

Thank you for your help.

If I shouldn’t ask about that quesion in this topic, so sorry.
- Marked as answer by
  
  Wednesday, December 22, 2010 2:23 AM

Источник

Causes of Access Database 3151 Error – ODBC Connection Failed

Methods to Fix Access Database 3151 Error – ODBC Connection Failed

Method 1: Check the ODBC Credentials

Method 2: Enable Named Pipe Setting

Method 3: Enable Tracing Options

Method 4: Verify the TCP Connection

Method 5: Check the ODBC Connection Database Setup

Method 6: Repair your Access Database

Closure

About The Author

Some of the symptoms that you might experience in Access Runtime Error 3151 are:

Causes of Access Runtime Error 3151

Ways to troubleshoot MS Access Runtime Error 3151

1. Method to manually resolve Access Error 3151

2. Virus Scan or Malware scan

3.Use Window System Restore

4.Install updates related to Windows

5. Uninstall and Reinstall MS Access Program

6.Professional MS Access Database Recovery Tool

Conclusion

Question

Answers