将访问权限转换为 PostgreSQL？

Convert Access to PostgreSQL?

提问by Jeffrey Melloy

A client has outgrown their Access database, and now I need to convert it to PostgreSQL. I don't have a lot of Access experience, so at first blush it seems like the best solution is to just export the data using text files or something.

一个客户已经超出了他们的 Access 数据库，现在我需要将其转换为 PostgreSQL。我没有很多 Access 经验，所以乍一看似乎最好的解决方案是使用文本文件或其他东西导出数据。

Does anyone have any good suggestions for an easy way to get the DDL and data out of Access and into something real?

有没有人有什么好的建议可以让 DDL 和数据从 Access 中取出并变成真实的东西？

回答by Tometzky

• Install Postgres ODBC driver on Windows computer.
• Create a data source with "PostgreSQL Unicode" driver to your new database
• For every table:
  • Use "File -> Export" choose type "ODBC Databases"
  • Confirm table name
  • Choose "Computer data source"
  • Select your data source

• 在 Windows 计算机上安装 Postgres ODBC 驱动程序。
• 使用“PostgreSQL Unicode”驱动程序为新数据库创建数据源
• 对于每张桌子：
  • 使用“文件-> 导出”选择类型“ODBC 数据库”
  • 确认表名
  • 选择“计算机数据源”
  • 选择您的数据源

Works well if you do not have too many tables. I needed to automate this so I have created an VBS script which just issued keyboard strokes in proper time, like this:

如果您没有太多表，则效果很好。我需要自动执行此操作，因此我创建了一个 VBS 脚本，该脚本仅在适当的时间发出键盘敲击，如下所示：

set shell=CreateObject("Wscript.Shell")
shell.Run("db1.mdb")
WScript.Sleep(5000);

shell.sendkeys("tablename1");
WScript.Sleep(1000);
shell.sendkeys("%fx"); ' Menu File, Export
WScript.Sleep(1000);
shell.sendkeys("%todbc"); ' Type: ODBC Databases
shell.sendkeys("~"); ' Enter

etc.

等等。

回答by mic

Based on http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.htmlbut updated for Access 2019 with more details:

基于http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html，但针对 Access 2019 更新了更多详细信息：

1. Install the PostgreSQL ODBC drivers.

   1. You may have this already installed from installing PostgreSQL. If not, you can install them from https://www.postgresql.org/ftp/odbc/versions/msi/.
   2. You should install the 32-bit driver if your Access is 32-bit and the 64-bit driver if Access is 64-bit. You can see if Access is 32-bit or 64-bit through File > Account > About Access.
   3. Uninstall old versions of the PostgreSQL driver before installing a new one.
   4. See Setting up PostgreSQL ODBC on Windowsfor more tips.

2. External Data > New Data Source > From Other Sources > ODBC Database. The keyboard shortcut for this is Alt > X > N1 > T > C.

3. Choose Link to the data source by creating a linked table, and press OK.

4. Next to DSN Name, click New...

5. Choose PostgreSQL Unicodeif your databases is UTF-8 or a non Latin Encoding. Choose PostgreSQL ANSI if your database encoding is SQL_ASCII, EUC_JP, BIG5, Shift-JIS, or a LATIN character set. Databases made from Access 2000 or later are in UTF-8. Choose the 64-bit version if Access is 64-bit. Click Next >.

1. 安装 PostgreSQL ODBC 驱动程序。

   1. 您可能已经通过安装 PostgreSQL 安装了它。如果没有，您可以从https://www.postgresql.org/ftp/odbc/versions/msi/安装它们。
   2. 如果您的 Access 是 32 位，则应安装 32 位驱动程序，如果 Access 是 64 位，则应安装 64 位驱动程序。您可以通过“文件”>“帐户”>“关于 Access”查看 Access 是 32 位还是 64 位。
   3. 在安装新驱动程序之前卸载旧版本的 PostgreSQL 驱动程序。
   4. 有关更多提示，请参阅在 Windows 上设置 PostgreSQL ODBC。

2. 外部数据 > 新数据源 > 从其他来源 > ODBC 数据库。键盘快捷键是 Alt > X > N1 > T > C。

3. 选择通过创建链接表链接到数据源，然后按确定。

4. 在 DSN 名称旁边，单击新建...

5. 如果您的数据库是 UTF-8 或非拉丁编码，请选择PostgreSQL Unicode。如果您的数据库编码是 SQL_ASCII、EUC_JP、BIG5、Shift-JIS 或 LATIN 字符集，请选择 PostgreSQL ANSI。从 Access 2000 或更高版本制作的数据库采用 UTF-8。如果 Access 是 64 位，请选择 64 位版本。单击下一步 >。

1. As the dialog says, "Type in the name of the file data source you want to save the connection to. Or, find the location to save to by clicking Browse." Click Next >. Click Finish.

2. Fill in the fields Database, Server, Port, User Name, and Password.

3. Click Connectionand uncheck Bools as char.

7. 正如对话框所说，“输入要将连接保存到的文件数据源的名称。或者，通过单击浏览找到要保存到的位置。” 单击下一步 >。单击完成。

8. 填写字段数据库、服务器、端口、用户名和密码。

9. 单击Connection并取消选中Bools as char。

1. Click Page 2and check True is -1, and uncheck Updatable Cursors. Click OK. Click OK.

2. If you get an error that says "A connection could not be made using the file data source parameters entered", open ODBC Data Sources (64-bit)from the Start menu (or ODBC Administrator from Control Panel for Windows 7 or earlier), click Add..., choose PostgreSQL Unicode(x64), click Finish, enter the details for your database, and click Test.

9. 单击第 2 页并选中True is -1，然后取消选中Updatable Cursors。单击“确定”。单击“确定”。

10. 如果您收到“无法使用输入的文件数据源参数建立连接”的错误消息，请从“开始”菜单（或 Windows 7 或更早版本的“控制面板”中的“ODBC 管理器”）打开ODBC 数据源（64 位），单击Add...，选择PostgreSQL Unicode(x64)，单击Finish，输入数据库的详细信息，然后单击Test。

1. Select the newly created .dsn file and click OK.

11. 选择新创建的 .dsn 文件，然后单击“确定”。

12. Now select the tables you want and click Save Password. If you are missing primary keys on tables, Access will prompt you for what fields or set of fields you would like to use as the primary key. This doesn't make any structural changes to the actual table, but in the linked structure, Access will pretend this is the primary key and use that accordingly for table updates and such. This is particularly useful for views where the concept of primary keys does not exist and you want your updateable views to be updateable from Access. If you click OK or Cancel to the question without picking a set of fields, that table will be marked as readonly, which is the desired behavior for a lot of reporting views.

12. 现在选择您想要的表并单击Save Password。如果缺少表上的主键，Access 将提示您输入要用作主键的字段或字段集。这不会对实际表进行任何结构更改，但在链接结构中，Access 会假装这是主键，并相应地将其用于表更新等。这对于不存在主键概念并且您希望可更新视图可从 Access 更新的视图特别有用。如果您对问题单击“确定”或“取消”而不选择一组字段，则该表将被标记为只读，这是许多报告视图所需的行为。

To export data from an Access table to PostgreSQL:

要将数据从 Access 表导出到 PostgreSQL：

1. Select the table.

2. Rename the table to what you want it to be named.

1. 选择表。

2. 将表重命名为您希望其命名的名称。

3. Make sure the default schema of the user you are using in Postgres, is the schema you want to export the data to.

3. 确保您在 Postgres 中使用的用户的默认架构是您要将数据导出到的架构。

1. External Data > More (in the Export section) > ODBC Databaseand select the DSN you created.

   1. One gotcha here is that PostgreSQL will maintain the casing of the fields in the table and the table name, so it's best to rename all your fields to lowercase first so you don't have to be quoting them every time you use them.

4. 外部数据 > 更多（在导出部分）> ODBC 数据库并选择您创建的 DSN。

   1. 这里的一个问题是 PostgreSQL 将维护表中字段的大小写和表名，因此最好先将所有字段重命名为小写，这样您就不必每次使用它们时都引用它们。

回答by MarkusQ

You should be able to write something that can see them both with ODBC or something, but failing that you could dump it to a text fileor use a commercial tool.

您应该能够编写一些可以使用 ODBC 或其他工具查看它们的内容，但如果失败，您可以将其转储到文本文件或使用商业工具。

回答by kermatt

This is an old script that I has not been updated in a while, but I used for a similar purpose: http://code.activestate.com/recipes/52267/

这是一个我有一段时间没有更新的旧脚本，但我用于类似目的：http: //code.activestate.com/recipes/52267/

Another commercial option: http://www.datanamic.com/dbzipper/index.html

另一个商业选项：http: //www.datanamic.com/dbzipper/index.html

回答by Brian Burns

Access is a great program, but is hindered by its inability to export or connect to external ODBC sources easily.

Access 是一个很棒的程序，但由于无法轻松导出或连接到外部 ODBC 源而受到阻碍。

Using the ODBC driver would be ideal, but the setup looks kind of daunting.

使用 ODBC 驱动程序将是理想的，但设置看起来有点令人生畏。

So I just exported the tables one by one to CSV files, then imported each one to the Postgres db through DBeaver, a really nice, free db admin tool - check it out here - https://dbeaver.io.

所以我只是将表一个一个地导出到 CSV 文件，然后通过 DBeaver 将每个表导入到 Postgres 数据库中，DBeaver 是一个非常好的免费数据库管理工具 - 在这里查看 - https://dbeaver.io。