Can you integrate Amazon S3 (or Cloud Files or Smiliar) with MS Access (VBA)?

When we build cleint applications we quite normally build a MS Access front end for internal use and a ASP.NET / PHP web front end for some smaller features for external users.

One problem we run into all the time is what about access to shared files.

I would love to upload files from both frontends to an Amazon S3 bucket or Rackspace Cloud Files. This is easy for the web frontend, but can anyone tell me how to integrate MS Access (VBA) with these services?

Thanks 开发者_运维百科Paul

I know very little about VBA in Access, but I'll assume that this is your area of expertise and can work out the details on that side.

Downloading

Downloading files from S3 is easy, you just need to link to the correct S3 path for you files. For public files they are in the format:

http://s3.amazonaws.com/[bucket]/[key]

If your content is private you'll need to create a signed url but all the .Net SDK or PHP SDK make this easy. You could create a web service on your web server to generate thses signed urls and use the service from the web and Access front ends.

Uploading

I assume you already have a way to upload from Access to your web server? eg a web service again? Instead of trying to upload directly to S3 from the web or Access frontends, just upload to the web server and then use the SDKs to upload from there to S3.

Indexing your files

Do not try to search or list files directly on S3. It is relatively slow, limited and would be difficult to do from VBA. Instead, store data about each uploaded file in your database and use that to search or query against.

Here is a spreadsheet with VBA that can do ListObjects, PutObject, GetObject using a secret key pair. xlsm file with VBA REST invocations

You're probably better off installing the aws cli on the server that has the MS Access database and running a shell command to execute an AWS S3 copy command:

oShell.run "aws s3 cp " & file-to-upload-path & " s3://your-bucket/ "