3151 odbc ошибка подключения - Решение и исправление самых разных ошибок на TopOshibok.ru

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.

Источник

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,8501 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.9k4 gold badges46 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

Источник

When using the ODBC protocol to connect to Microsoft Access, you may experience run time error 3151 ODBC connection failed. Here is the complete error that pop-up on your screen:

The above error triggers due to several reasons and can be easily fixed. In this write-up, I will discuss the possible causes and effective workarounds to get rid of the MS Access ODBC connection failed error. Also, I’ll mention the professional Access Repair & Recovery Tool to solve this error with less effort if it arises due to database corruption.

Rated Excellent on Trustpilot
Free MS Access Database Repair Tool
Repair corrupt MDB and ACCDB database files and recover deleted database tables, queries, indexes and records easily. Try Now!

Download
By clicking the button above and installing Stellar Repair for Access (14.8 MB), I acknowledge that I have read and agree to the End User License Agreement and Privacy Policy of this site.

Quick Fixes:

Fix “With SQL Server Authentication” Option
Set Up An ODBC Connection For An Access Database
Enable the Tracing Options
Enable the Settings of Named Pipe
Checking TCP Connection
Repair Corrupt Access Database

But before proceeding further, let’s know something about ODBC & ODBC – connection to <name> failed. (Error 3151).

Short Information On ODBC Data Sources

ODBC (Open Database Connectivity) is one such protocol that is mainly used to connect MS Access database to any other external data sources like Microsoft SQL Server. On the other hand, ODBC Data Source is a combination of source data and the connection information which is needed to access that particular data. Here are a few examples of Data Source: SQL Server, a spreadsheet, Oracle RDBMS, and text files.

Whereas an example of connection information comprises server location, database name, login ID, password, and various ODBC driver options which define how to get connected to data sources. Well, such information can be achieved from the database administrator to which you want to get connected. In Access, ODBC data sources are used to connect data sources external to Access that doesn’t contain any built-in drivers.

Odbc Connection Failed Error is a very common claimed error message by several of the access database users. It is observed that this type of ODBC Connection issue generally arises when some changes are done in SQL servers with a new name, IP and DSN; or the creation of a new ODBC and modification done in link tables.

On the other hand, we can say, error code 3151 is a result of ODBC connection failure in MS Access. However, there are countless causes that may lead to this error. But below I have listed the major ones:

Network connection problem
Inappropriate SQL Server Authentication.
Access database corruption.
Making changes to the link tables
When changes are made in SQL servers, like providing a new name, IP and DSN.
Windows Firewall is restricting the connection
Improper TCP or Tracing settings.

How to Fix Access Run Time Error 3151 ODBC Connection Failed?

To fix the Access database ODBC–Connection To Failed error, here are the possible fixes mentioned. Let’s see what the first method says:

Way 1- Fix “With SQL Server Authentication” Option

At the time of the creation of an ODBC connection to the SQL server, make the selection of the login ID and password given by the user. Make confirmation of the same thing in Microsoft Access, where System Data Source and Machine Data Source is been used to create the ODBC connection.

Here is how to perform this procedure –

Open your MS Access Database >> go to a File menu >> click on Get external data >> Link tables options.

Now from the File Types list, choose the ODBC database.

Hit the Machine Data Source option.
Tap to the New option.
Choose the System Data Source and select the Next option.
Now hit the SQL Server option. After that tap to the Next and then finish option.

Assign a name for ODBC connection and SQL Server in the given text boxes.
Hit the next option and make a check over the “With SQL Server authentication using a login id and password entered by the user” option.
Hit the Next and select the appropriate database.

Click Next and Finish. At this time you may test the ODBC connection to SQL Server.
Click OK more than a few times until you come back to the SQL Server login page.
Enter the login ID and password to the SQL server database and hit the OK option.
Now, select Save Password checkbox.

After trying out the above steps now you must have overcome the issue of ODBC Connection for an Access Database. But if in case it won’t get success, approach the second one.

Way 2- Set Up An ODBC Connection For An Access Database

It’s important to perfectly set up the ODBC connection for the Access database. So, just check out whether you have done it correctly or not. This will also help you in learning how to set up an ODBC connection for Access Database with form error.

1. On your Windows desktop tap to the Start >Settings > Control Panel > open the Administrative Tools control panel.

2. After opening of the Administrative Tools window makes double-click on the Data Sources (ODBC).

3. Now In the ODBC Data Source Administrator dialog box, hit the Add.

4. After that in opened the Create New Data Source dialog box, hit the Microsoft Access Driver (*.mdb), and then to the Finish

5. For assigning the data source name to enter Parcel_Owners, in the ODBC Microsoft Access Setup dialog

6. Also, enter its description like “Parcel owner information.”

7. In the same dialog box under the Database option, click the Select.

8. After this Select Database dialog box will get open on your screen, search for the sample files, and choose the Assessor.mdb file. You need to specify the database for the data sources.

9. Tap to the OK option in all the opened Select Database, ODBC Microsoft Access Setup, and ODBC Data Source Administrator dialog boxes.

10. After completing this entire task, close the Administrative Tools control panel.

Way 3- Enable the Tracing Options

Many users have reported that they received run time error 3151 ODBC connection failed due to an issue with the ODBC tracing capabilities.

Well, you can easily troubleshoot this error by configuring the ODBC Administrator Tracing tab.

Here is how you can do so:

Sign in to Local Administrators group.
Next, go to Administrative Tools >> click on “ODBC Data Source Administrator”.
Choose a Tracing tab.
Select the checkbox “Machine-Wide Tracing for all identifies”.
Select the machine-wide tracing checkbox to enable it.
Now, clear a checkbox to go back to per user tracing >> hit Apply.

Way 4- Enable the Settings of Named Pipe

Generally, the Named Pipe protocol is used to establish the connection between the network users & MS SQL Server. However, the Open Database Connectivity (ODBC) connection might fail if the Named Pipe protocol is turned OFF.

You can simply follow the steps below for enabling this protocol’s setting:

Go to the Control Panel on your PC.
On the page of “All Control Panel Items” >> click Windows Administrative Tools.
Then, make double-click on the Local Security Policy.

Under the Local Security Policy, you have to click on Security Settings >> Local policies.
After this, click on Security Options>> right-tap on the “Network access: Named Pipes that can be accessed anonymously” option >> choose Enabled option.

Way 5- Fix Run Time Error 3151 ODBC Connection Failed by Checking TCP Connection

You can simply check for the active IP Statistics & TCP connection by following the beneath steps:

Step 1- First, press Win + R keys then enter ‘CMD’ >> OK to launch the Command Prompt window.

Step 2- Under Command Prompt >> enter the below command:

netstat -n –a.

Way 6- Repair Corrupt Access Database

As mentioned in the causes section, corruption in the database can also trigger a connection to <name> failed. (Error 3151). In such a case, you can use the Access in-built Compact & Repair tool.

Here is how you can do so:

Open your Access database.
Click on File then Info.
After this, find and click on Compact & Repair Database.

Recommended Option to Keep Access Database Safe And Error Free:

If you want to keep your access database files safe-side and free from any corruption glitches then also keep a proper and updated backup of your Access database.

But if you are already dealing with the access database corruption issue or the error conquered then also you need not worry about it. Just try the professional Access Database Repair And Recovery tool to effectively repair Access database MDB/ACCDB files.

This MS Access recovery software works efficiently to repair corrupt/damaged Access databases. Apart from that the software deeply scans every segment of the corrupted access database and removes all glitches from it. The tool is just perfect to recover all the items of Access Database files like Tables, Queries, Indexes, Macros, etc. safely and securely without any issue.

* By clicking the Download button above and installing Stellar Repair for Access (14.8 MB), I acknowledge that I have read and agree to the End User License Agreement and Privacy Policy of this site.

Steps to Use Access Repair Tool

access-repair-main-screen

access-repairing-completed

Final Verdict

The Access runtime error 3151 ODBC connection failed can occur due to various reasons. However, you can follow the aforementioned methods to troubleshoot this error.

In case, if this error arises due to corruption in the database, you must opt for Access Repair Tool. It’s designed with a simple UI for repairing corrupt database files in no time. Also, it can help you to recover all the objects from a corrupted file without any data loss.

I hope you liked this post.

tip Still having issues? Fix them with this Access repair tool:

This software repairs & restores all ACCDB/MDB objects including tables, reports, queries, records, forms, and indexes along with modules, macros, and other stuffs effectively.

Download Stellar Repair for Access rated Great on Cnet (download starts on this page).
Click Browse and Search option to locate corrupt Access database.
Click Repair button to repair & preview the database objects.

Pearson Willey is a website content writer and long-form content planner. Besides this, he is also an avid reader. Thus he knows very well how to write an engaging content for readers. Writing is like a growing edge for him. He loves exploring his knowledge on MS Access & sharing tech blogs.

Источник

-55 / 4 / 0

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

Сообщений: 365

14.12.2021, 17:56. Показов 636. Ответов 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

10065 / 5142 / 1283

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

Сообщений: 14,472

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 я устновил. Проблема осталась.
Я получил два файла, которые возможно должны мне помочь в установке соединения. Но так ли это я не знаю.

Миниатюры

10065 / 5142 / 1283

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

Сообщений: 14,472

14.12.2021, 19:15

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

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

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

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

Источник

I have an MS Access application that links to a SQL Server 2008 db for all of its data. For years, it has worked fine when the SQL Server was on Win2008 Standard 64-bit SP2 and SQL Server 2008 v10.0.4. I recently moved the back-end SQL db to a newer server (Win2008 R2 Standard 64-bit and SQL v10.50.1600). After the move, everything on the application worked just fine as expected, no issues. However, we have a month-end process that generates relatively large reports (300-500 pages) which uncovered an issue that we don’t normally see in our day-to-day processing.

My report is based on a pass-through query. The query runs almost instantaneously and produces 8000 rows. I can preview the query and quickly navigate to the end of the RS to see the 8000’th row. No problem. If I pull up the report that’s based on that query, I can see that there are 350 pages and I can navigate page-by-page through the report with no problem. I can even type in a page number and jump to page 50, 100, 150, etc. with no problem. Note, there is no processing/code/logic happening on any fired events on the report — just displaying and formatting the data.

HERE IS WHERE THE PROBLEM OCCURS: If I preview the report and try to jump to the last page from the first page, it’ll sit and think for a while (38 seconds to be exact) and then throw Error #3151 ODBC Connection to [DNS Name] Failed.

If I simply update the connect string on the pass-through query to point it back to the «older» SQL Server and run the same thing, it works just fine. I’ve tried adjusting the connect string to use IP address instead of DNS name, no luck. This is a process that’s worked for years with no problem so I know the client-side logic is sound.

Any thoughts or suggestions?

Источник