Как связать Access с MySQL
В статье рассказывается как связать MS Access с базой данных MySQL, чтобы данные хранились не в файле mdb, а в таблицах MySQL.
1) Нужно поставить MySQL сервер, скачав по адресу mysql.com. Ставится легко, главное указать пароль к серверу и не забыть его.
2) Нужно установить ODBC driver, скачав по адресу mysql.com. Без него Access не сможет подключаться к MySQL.
3) Access умеет работать со связанными (linked) таблица, но они уже должны быть уже созданы! На рисунке видно что создана база данных (database) my_access с таблицей mytable1. В mytable1 созданы 2 поля id и FIO для примера. Создавать базу данных и таблицы можно любым доступным способом, я использовал MySQL Query Browser из mysql-gui-tools-5.0-r17-win32.msi. Этот этап называется проектированием базы данных и тут подробно расписывать не буду, это отдельная история. Смысл один! Нужно создать базу данных и нужные таблицы с нужными полями.
4) Создание связей. Запустите Access и в нем создайте базу данных и сохранитесь. Не создавайте таблиц и что либо. Откройте меню Файл — Внешние данные — Связь с таблицами. В окне связь укажите ODBC Databases () как на рисунке
5) В появившемся окне Выбор источника данных переключитесь на закладку Источник данных компьютера и нажмите кнопку Создать. Выберите MySQL ODBC Driver как на рисунке.
6) В окне от MySQL заполните поля Data Source Name, укажите Server — в это поле нужно вписать адрес сервера в сети, если Access и MySQL находятся на одном компьютере, то можно вписать localhost (это адрес 127.0.0.1). Если MySQL находится на другом компьютере, то нужно указать адрес того компьютера, для этого понадобится помощь администратора сети и/или баз данных. Укажите User и Password, Имя и Пароль для доступа к базам данных, эти данные должны быть запомнены при установке MySQL или выданы администратором. Обязательно укажите к какой базе данных подсоединятся (на примере my_access созданная на этапе 3. Нажмите кнопку Test для проверки правильности подключения, ответ должен быть успешным Connection successful.
7) После указания параметров подключения вы вернетесь на этап Выбор источника данных. Выберите только что созданное соединение с MySQL (у меня это access2mysql), укажите таблицы для связи.
8) Можно работать со связанными таблицами как с обычными, только данные будут хранится не в файле mdb, а в базе данных MySQL. На рисунке показано, что введённые в таблицу mytable1 в Access, хранятся в MySQL и это показывает MySQL Query Browser
Межсерверный перенос данных Перенос данных между серверами с помощью специализированных утилит.
Для переноса данных больших размеров с сервера, например с MS SQL на MYSQL или в другой формат в графическом режиме можно использовать специализированные утилиты. Некоторые из них: Navicat — Для полноценной работы необходима лицензия. DBSync for MsSQL & MySQL — Для полноценной работы необходима лицензия. SypexDumper – работа с web страниц. Свободна распространяемая. Для работы в ней необходим web сервер. phpMyAdmin – работа с web страниц. Для работы в ней необходим web сервер. Для переноса больших размеров данных необходимо настроить конфигурацию минимальный размер 10.240 kb. MySQL-Front – русскоязычный интерфейс для полноценной работы необходима лицензия. Для переноса данных используется экспорт, и импорт данных предварительно создаётся база данных, в которую после переносятся таблицы.
Подробней остановимся на утилите MySQL Migration Toolkit. Переносит данные более корректно и быстро, нет проблем с переносом ключей, индексов, нет необходимости устанавливать ODBC драйвера. Дистрибутив входит в состав mysql gui tools. Если возникают проблемы с подключением к MS SQL, утилита должна быть установлена на этом же ПК, это связано с безопасностью SQL сервера. Также необходимо чтобы бала установлена Java иначе не будет работать. Утилита сама создаёт базу данных, но к названию добавляет dbo. Для переименования базы данных можно воспользоваться MySQL Administrator, она также входит в состав mysql gui tools, создав дамп, а затем восстановив его в базе с подходящим названием. Основы администрирования MYSQL.
Межсерверный перенос средствами MySql Migration Toolkit
Предварительно устанавливаем утилиту mysql-gui-tools. В меню пуск, все программы находим ярлык MYSQL и запускаем программу.
Запускаем утилиту и пошагово водим требуемые значения. Выбираем, с какого сервера будут копироваться данные, в примере выберем подключения к MS SQL Server. Вносим данные имя сервера, если установлен на этом же ПК пишем localhost, имя пользователя по умолчанию администратор sa, если есть пароль, в поле database имя базы данных для просмотра щелкаем на кнопке рядом с полем.
Если MYSQL установлен на другом ПК. В настройках сервера разрешаем Enable root access from remove machines — доступа с удаленного компьютера. Запускаем Reconfigure Instance и устанавливаем галочку. Далее в следующем окне MySQL Migration Toolkit подключаемся к серверу MYSQL. В поле Hostname IP адрес, имя пользователя, по умолчанию администратор root и пароль.
В MySQL Migration Toolkit есть Help с подробным описанием всех настроек программы. Если возникают трудности в выборе необходимых настроек, не открывайте функцию дополнительных значений, они по умолчанию настроены как необходимо. Можете использовать следующий метод:
How to Import a SQL Server Database into Access 2016
Access 2016 provides a wizard to help you import a SQL Server database into Access.
You can import a whole SQL Server database, or part of it, into an Access database. When you import a database into Access, you effectively take a copy of the database objects and its data. Therefore, any updates you make in Access is not reflected in the original SQL Server database. You can update the data and objects and it won’t impact on the source database.
If you need all data changes to be reflected in the source database, you need to create linked tables to SQL Server instead. Doing that will keep allow you to update the data in the source database. However, you won’t be able to change the table definitions from Access. Any structural changes need to be done on the SQL Server end.
Below is a step-by-step guide to importing a SQL Server database into Access 2016 (and creating a new data source in the process).
Launch the Import/Link Wizard
Click ODBC Database in the Import & Link group from the External Data tab on the Ribbon.
You can start from an existing Access database, or you can create a blank database first.
Select the «Import» Option
Select Import the source data into a new table in the current database , then click OK .
Select the Data Source
This screen is asking for a data source. You can either create a new data source, or provide the details of an existing one.
If you already have a data source for SQL Server, skip to step 12.
Otherwise, you will need to create a data source here.
For this example, we will create a new data source.
To create a data source, click New. .
Select a Driver
Select SQL Server from the list of drivers, then click Next > .
The following steps may differ slightly, depending on the software installed on your machine.
Enter the Data Source Name
Type the name of the data source you’d like to save the connection to (or browse to a location using the Browse. button), then click Next > .
Create the Data Source you just Configured
Review the information and click Finish .
The «Create a New Data Source to SQL Server» Wizard
We still need to provide more details for our connection to SQL Server.
Provide a description for your data source, select the SQL Server that you’d like to connect to, then click Next > .
If the database is on the same server you can select (local) .
Choose an Authentication Method
Select either Windows NT authentication or SQL Server authentication , then click Next > .
Client Configuration
Click Client Configuration if you want the connection to use a network library other than the client’s default network library.
Also use this option if the actual network address of the server must be specified for a successful connection. For example, when using the TCP/IP Net-Library, you might need to specify the port and socket address of the server. If a SQL Server instance is listening on an alternate named pipe, you must specify the pipe name in the advanced entry.
Set the Default Database
Change the default database to the actual database that contains the tables/views you want to link to.
Change any other settings if required.
When ANSI quoted identifiers is selected, SQL Server enforces ANSI rules regarding quote marks. Double quotes can only be used for identifiers, such as column and table names. Character strings must be enclosed in single quotes.
Selecting ANSI nulls, paddings, and warnings specifies that the ANSI_NULLS , ANSI_WARNINGS , and ANSI_PADDINGS options be set on when the SQL Server driver connects.
ANSI_NULLS requires that IS NULL or IS NOT NULL is used for all NULL comparisons. The Transact-SQL syntax = NULL is not supported.
ANSI_WARNINGS specifies that SQL Server issues warning messages for conditions that violate ANSI rules but do not violate the rules of Transact-SQL.
ANSI_PADDING specifies that trailing blanks on varchar values and trailing zeroes on varbinary values are not automatically trimmed.
Specify any Extra Options
Modify any settings as required, then click Finish .
Review the ODBC Data Source Settings
Review the summary, then click OK .
You can (optionally) click Test Data Source. first to check to see if it’s going to work.
Select Data Source
We’re now back at the original dialog box that asks us to select a data source.
We now have a DSN file so we can continue on and connect to SQL Server.
Ensure that the DSN file’s folder is selected next to Look in and the DSN file is entered next to DSN Name , then click OK .
Select which Tables/Views to add to Access
The wizard displays the tables and views from SQL Server so that you can select the ones you require.
Select all tables and/or views that you want to be imported into Access, then click OK .
Select Unique Identifier
This screen is only displayed if Access can’t find a unique identifier for a table.
If Access is unable to determine which field or fields uniquely identify a table or view, you will be prompted to set the unique identifier for that table.
Select the field/s that uniquely identify each record, then click OK .
You will need to do this for each table where the unique identifier can’t be identified.
This step will set the selected fields as primary keys in Access. You can verify this by viewing the table in Design View later.
Close or Save Import Steps
The import has now completed. You can either close the wizard or save the import steps so that you can run the same import later without needing to go through all the steps in the wizrd.
To close the wizard, click Close .
To save the import steps, click Save import steps , enter a name and description, optionally select Create Outlook Task , then click Save Import .
The Result
The imported tables now appear in the left Navigation Pane.
The table names are prefixed with the schema name from the SQL Server database. For example, in SQL Server, a table might be Application.Cities , whereas, the imported table would be called Application_Cities .
The database has now been imported from SQL Server. You can now make any change to the database in Access without impacting on the original database in SQL Server.
About the SQL Server Database in the Above Example
The SQL Server database used in the above example is the WideWorldImporters database. This is a sample database provided by Microsoft’s SQL Server team for the purposes of demonstrations, training, etc. If you’d like to install this database to SQL Server, I have step-by-step instructions here.
Как подключить бд sql к access
Недавно для одной из рабочих задач встала необходимость создания клиентского приложения для работы с базой данных на платформе MS SQL Server 2000. По условиям ТЗ клиент должен был быть выполнен в MS Access.
Ниже я привожу код, который позволяет создать подключение к SQL Server без предварительного создания DSN и создать ссылки на серверные таблицы в Access.
‘ Функция проверки существования таблицы в Access
‘
‘ Parameters
‘ tblName — название таблицы
‘
‘ Return Value
‘ True — если таблица с заданным именем существует, False — в противном случае
Public Function isTableExist(tblName) As Boolean
On Error Resume Next
Dim td As TableDef
Set td = currentDB.TableDefs(tblName)
If Err.Number = 0 Then
If td.Connect <> «» Then
isTableExist = True
End If
Set td = Nothing
End If
Err.Clear
End Function
‘ Функция создания ссылки в Access на таблицу в базе данных MS SQL Server
‘
‘ Parameters
‘ serverName — имя или IP сервера
‘ serverDBName — название серверной базы данных
‘ tblName — название таблицы
‘ makeHidden — сделать таблицу скрытой (Boolean)
Public Sub linkTable(serverName, serverDBName , tblName, makeHidden)
‘ Если в Access уже существует таблица с таким именем ее необходимо удалить
If (isTableExist(tblName) = True ) Then
DoCmd.DeleteObject acTable, tblName
End If
‘ Создание ссылки на серверную таблицу в Access
DoCmd.TransferDatabase acLink, «ODBC», _
«ODBC;DRIVER=SQL Server;» & _
«DATABASE=» & serverDBName & «;» & _
«APP=Microsoft Office 2003;» & _
«SERVER=» & serverName & «;» & _
«Trusted_Connection=Yes;» & _
«», acTable, tblName, tblName, False, True
‘ Если makeHidden = True таблица будет скрытой
Application.SetHiddenAttribute acTable, tblName, makeHidden
End Sub
В процессе выполнения предыдущей процедуры (создания ссылки), если серверная таблица не имеет ключевых полей, будет показано диалоговое окно, с предложением создания ключевых полей. Если отказаться от выбора ключевых полей редактирование и создание новых записей будет невозможно. Доступ к таблицам будет осуществляться только в режиме чтения.
Если необходимо предоставить доступ к серверным таблицам только для чтения и в подключаемых серверных таблицах нет ключевых полей, например, при создании ссылок на серверные представления (View), удобно воспользоваться следующей функцией. При создании ссылок на серверные таблицы предложений о выборе ключевых полей появляться не будет.
‘ Функция создания ссылки в Access на таблицу в базе данных MS SQL Server
‘
‘ Parameters
‘ serverName — имя или IP сервера
‘ serverDBName — название серверной базы данных
‘ tblName — название таблицы
‘ makeHidden — сделать таблицу скрытой (Boolean)
Public Sub linkView(serverName, serverDBName , tblName, makeHidden)
‘ Если в Access уже существует таблица с таким именем ее необходимо удалить
If (isTableExist(tblName) = True ) Then
DoCmd.DeleteObject acTable, tblName
End If
‘ Создание подключения к серверу
Set NewTableDef = currentDB.CreateTableDef(tblName)
NewTableDef.Connect = «ODBC;DRIVER=SQL Server;» & _
«UID=;» & _
«DATABASE=» & serverDBName & «;» & _
«SERVER=» & serverName & «;» & _
«PWD color: rgb(0, 102, 0);»>’ Создание ссылки на серверную таблицу в Access
currentDB.TableDefs.Append NewTableDef
‘ Если makeHidden = True таблица будет скрытой
Application.SetHiddenAttribute acTable, tblName, makeHidden
Set NewTableDef = Nothing
End Sub
И, наконец, следующая функция иллюстрирует создание подключения к MS SQL Server без необходимости предварительного ручного или программного создания DSN.