Access 2007 to SQL server file upload?!

Question

I have an Access 2007 database with an attachments facility. Currently the client may upload files locally but the files cannot be accessed elsewhere. I have been able to carry out a similar operation when developing on a web based system however I cannot seem to do it on an Access 2007 database and I am unsure as to whether it is even possible. Basically the system needs to connect to the SQL server online and upload the file although the database is not online itself. I would be grateful for any pointers!

Answer 1

I have faced this situation. Here are your choices:

1. Use Access attachment field in a shared ACCDB -- won't work "online" very well, but you could park the ACCDB on your LAN and make it a separate back-end ACCDB shared by all. Your post didn't say whether your users are either local or "online" -- and whether "online" meant web.

2. Use VarChar(Max) (aka BLOB) fields in SQL-Server to store the attachments. But, you can't populate these easily from Access. Assuming you control the server where SQL-Server is running, you can use ADO in Access to upload a VarChar(Max) using the bulkinsert T-SQL command. This works pretty well and it's easy.

3. Create an upload web page. Use iExplorer automation (i.e, create an iExplorer object) in VBA to navigate to that page, fill it in and press the upload button. For security reasons, you cannot use automation to fill in a file upload control, but you can use sendkeys. This doesn't work perfectly -- sometimes you have to repeat the process once or twice, but it works pretty well if it's invoked by a user who can validate it's working. This is what I did -- easiest solution.

4. Best solution probably is to create a web service using WCF to handle the upload. There are plenty of posts on how to encode and decode byte arrays to store files as VarChar(Max). It works extremely well. Unfortunately, Access cannot directly consume web services as far as I've been able to tell, so you would have to write a small vb.net program to do this and call it from Access.

5. You could store the files/attachments outside of SQL/Server - just on the server, and store only the links/URL's for those files in Access. You could make each one launchable. This is easy but harder to control the security.

6. You can use Sharepoint to store/share the attachments. That can work pretty well depending on the size of the attachments and your connectivity. It's built to support this.

Access allows multiple attachments in one record. SQL/Server doesn't support this. So, if you can split your ACCDB into a front-end for the programs only and back-end ACCDB that is sharable by your users to contain the data/attachments, that is by far the easiest answer.