导出mysql表的最快/安全的方式进入ms访问表 [英] fastest/secure way for export mysql tables into ms Access table
问题描述
我正在寻找一种将一些MySql表导出到MsAccess数据库的其他表中的方法（我在谈论一百万条记录表...）
i'm searching for a way to export some MySql tables into other tables of a MsAccess DB (I'm talking about a million records table...)
我想到的唯一的两种方法是：从myadmin或者toad导出一个csv文件，然后把它导入到
the only two way that I thought about are:
- 访问数据库。
export from myadmin or toad a csv file and then import it into the access DB.
直接从Toad获取访问数据库导出
making directly from Toad the "Access Database Export"
我得到的是第一个更快，但更安全的数据完整性，而第二个是完美的数据完整性，但非常慢...有人知道其他方式？
I'm getting that the first is more fast but less secure for the data integrity, while the second is perfect for the data integrity but very slow ... someone knows other ways?
感谢
A.
Thanks A.
推荐答案
指导使用MySQL数据库在Web服务器上运行Access前端应用程序（您不需要导入表，您可以在Web服务器上使用您的msaccess应用程序）：
step by step guide to running Access frontend application with MySQL database on webserver (you dont need to IMPORT the tables, you can use your msaccess application WITH them on the webserver):
如果您正在运行MsAccess，我想你正在使用Windows
If you are running MsAccess, i suppose that you are using windows
- 安装MySQL ODBC 5.1驱动程序（连接器） http://dev.mysql.com/downloads/connector/odbc/
- 打开机器上的控制面板
- 管理工具（如果Vista或七，搜索ODBC）
- 设置数据源ODBC
- SYSTEM DSN
- 添加
- Install MySQL ODBC 5.1 Driver (connector) http://dev.mysql.com/downloads/connector/odbc/
- Open CONTROL PANEL on win machine
- ADMINISTRATIVE TOOLS (if Vista or Seven, search ODBC)
- SET UP DATA SOURCES ODBC
- SYSTEM DSN
- ADD
根据您的服务器，您可能找到服务器名称或IP的一些困难，寻找SSH数据库连接（或类似的东西）。作为示例，请阅读NetSol的常见问题解答： http://www.networksolutions.com/support/how-to-back-up-the-mysql-database-using-ssh/
depending on your server, you might have some difficulty finding the server name or IP, look for SSH Database connection (or something like that). as an example, read NetSol's FAQ: http://www.networksolutions.com/support/how-to-back-up-the-mysql-database-using-ssh/
一次你已经这样做了，在MsAccess中：
1.文件
2.外部数据源
3.链接表
once you have done that, in MsAccess: 1. FILES 2. EXTERNAL DATA SOURCE 3. LINK TABLES
如果你想要从MsAccess导出到MySQL，您可以在访问中创建一个FORM，在其上放置一个按钮，并在VBA中为OnClick（）事件创建此子文件：
if you want to EXPORT to MySQL from MsAccess, you can create a FORM in access, put a button on it, and in VBA create this sub for the OnClick() event:
Dim sTblNm As String
Dim sTypExprt As String
Dim sCnxnStr As String, vStTime As Variant
Dim db As Database, tbldef As DAO.TableDef
On Error GoTo ExportTbls_Error
sTypExprt = "ODBC Database"
sCnxnStr = "ODBC;DSN=DSNname;UID=userOnServer;PWD=pwdOnServer"
vStTime = Timer
Application.Echo False, "Visual Basic code is executing."
Set db = CurrentDb()
For Each tbldef In db.TableDefs
Debug.Print tbldef.Name
sTblNm = tbldef.Name
DoCmd.TransferDatabase acExport, sTypExprt, sCnxnStr, acTable, sTblNm, sTblNm
Next tbldef
MsgBox "Done!"
On Error GoTo 0
SmoothExit_ExportTbls:
Set db = Nothing
Application.Echo True
Exit Sub
ExportTbls_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportTblsODST"
Resume SmoothExit_ExportTbls
有时，在运行非英文窗口时，您可能会收到错误2507，更改ODBC数据库为ODBC（使用法语）。
sometimes, while running non-english windows you might get error 2507, change "ODBC Database" for "ODBC" (works with French).
这篇关于导出mysql表的最快/安全的方式进入ms访问表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
