3

I am writing a VBA script to import data from an Access database to a remote database server. (It happens to be PostgreSQL, but I don't think that's very relevant.) At the moment, my scripts are embedded inside an Access database. However, I would like to version my scripts like I do my other source code: as plain text so SVN can give me good change tracking.

I would also prefer not to have to deliver the source files to the client for them to use the script. In other words, I would like to deliver the import tool already compiled into the Access database. Is it possible to do so?

Or am I chasing the wrong rabbit? Is there a way I can use the Access libraries without embedding any code into the Access database? My searching suggests not, but I'm open to the possibility. This is the first thing I've ever done with VBA, so there's a ton I don't know, I'm sure.

Improve this question

2 Answers 2

Reset to default
2

I think I was going down the wrong rabbit hole (and I think I was using a non-existent idiom). Turns out that there are some .NET interop DLLs for Office. My machine seems to have the .NET interop DLLs already installed with Office, so all I had to do was add a reference to the Microsoft.Office.Interop.Access DLL and create an application object:

var app = new Microsoft.Office.Interop.Access.Application();

I think a .NET app is going to be more suited to my needs. I'm not going to be doing a lot of Access specific things. This will allow me to have my source under version control as usual. The only downside is that any development machine (and build server, if I automate the build) will need Access installed, but that was going to happen with a VBA script, anyway.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

If you don't want to do it within Access, you may want to tag this differently.

You can deliver your database to the client as an MDE file, which is a compiled and stand-alone Access database. The client will not be able to see the modules or other VBA code in this format, if that is your main concern.

Improve this answer

4 Comments

No, my main concern is just all the file shuffling that would have to be done. It's simpler for the client if they only have to deal with one file. If you can suggest some tags, I'd be very appreciative.
Take a look at the Shell() command in Access. You would have to make sure you distribute your VB script to the same location every time, but it seems like a pretty simple thing to do. I have used this functionality to launch apps from within Access, including FTP file transfers. Not sure if you care about anyone being able to find your export function if they're inquisitive enough, but it'll do what you're looking for.
"I am writing a VBA script" This is where the confusion starts with me. There are a few flavors of VB. Which are you actually using? "my scripts are embedded inside an Access database." More confusion! "I would like to deliver the import tool already compiled into the Access database." This is exactly how VBA (Visual Basic for Applications) operates. Can you explain where the code in your current solution is housed, where it is run and how. Don't be afraid to write a lot in order to be clear what you are currently doing.
I thought "VBA" is a variety of VB, namely the one associated with Office. =) Um, the Access database file currently holds my scripts? I'm using the built in script editor for Access (behind a form button, at the moment). As for the tool being compiled into the database, that may not be the case if I had a way to have the script be external to the mdb file; no? I'm also quietly wondering if you meant to comment on the question instead of the answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.