如何使用 Access2007 中的 VBA 代码将 PDF MP3 DOC XLS 等文件导入 MS SQL Server 数据表字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/302258/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to import a files like PDF MP3 DOC XLS into a MS SQL Server Datatable field using VBA code in Access2007
提问by Philippe Grondier
does anybody know how to save and retieve files in MS SQL-Server 2000? I guess the image data type could be used as a container.
有人知道如何在 MS SQL-Server 2000 中保存和检索文件吗？我猜图像数据类型可以用作容器。
I want to import/export the following file types: DOC, XLS, PDF, BMP, TIFF, etc.
我想导入/导出以下文件类型：DOC、XLS、PDF、BMP、TIFF 等。
Due to resource issues we are using MS-Access2007 as the front end, so I am looking for VBA code.
由于资源问题，我们使用 MS-Access2007 作为前端，所以我正在寻找 VBA 代码。
Thanks in Advance.
提前致谢。
回答by Philippe Grondier
I advise you (really!) not to try (ever!) to save files as data in a database. You will quickly face critical space problems.
我建议您（真的！）不要尝试（永远！）将文件另存为数据库中的数据。您将很快面临严重的空间问题。
Please think about creating folders for file storage. These will be used to save/archive your files. Folders paths can be stored in one of your tables (Tbl_Folder, for example). You can then store your file names in a table (such as Tbl_File, where you'll have a 'filename' field). You will be able to open it with the Access.followHyperlinkmethod and to manage it (copy/delete/move) with the File Scripting Object(FSO).
请考虑为文件存储创建文件夹。这些将用于保存/归档您的文件。文件夹路径可以存储在您的一张表中（Tbl_Folder例如）。然后您可以将您的文件名存储在一个表中（例如Tbl_File，您将有一个 ' filename' 字段）。您将能够使用该Access.followHyperlink方法打开它并使用文件脚本对象(FSO)对其进行管理（复制/删除/移动）。
回答by Mat Nadrofsky
You can do this using GetChunk and AppendChunk.
您可以使用 GetChunk 和 AppendChunk 执行此操作。
From this postyou might find this linkhelpful!
One thing to watch out for:
需要注意的一件事：
When using certain providers, most notably ODBC to SQL Server and other databases, you may have to take special care in retrieving BLOB data, such as placing BLOB columns at the end of the field list and referencing all non-BLOB fields prior to access BLOB columns.
使用某些提供程序时，尤其是 ODBC 到 SQL Server 和其他数据库时，您可能必须特别注意检索 BLOB 数据，例如将 BLOB 列放在字段列表的末尾并在访问 BLOB 之前引用所有非 BLOB 字段列。
Best of luck!
祝你好运！
回答by Birger
You can do this with streams. This code should help you with the first steps:
你可以用流来做到这一点。此代码应该可以帮助您完成第一步：
Set rs = New ADODB.Recordset
rs.Open "select * from YourTable", Connection, adOpenKeyset, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile "c:\myfile.pdf"
rs.Fields("blobfield").Value = mstream.Read
rs.Update
There is nothing dangerous about storing files in a database. We have a SQL Server database of about 20 GB which contains about 40.000 documents, images, etc. Never had a problem with it in over 3 years.
将文件存储在数据库中没有任何危险。我们有一个大约 20 GB 的 SQL Server 数据库，其中包含大约 40.000 个文档、图像等。3 年多来从未出现过问题。
