using in access 2013

Posted on 2014-09-22
Last Modified: 2014-10-06
In MS Access 2013 when I write code for an event the MS Visual Basic for Applications opens. Instead of writing the code in VBA is it possible to write the code in If so what do I need to do so I may use along with VBA.
Question by:Gary Samuels
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +3
LVL 27

Assisted Solution

MacroShadow earned 250 total points
ID: 40336552
No, Office applications don't directly support VB.Net. You could write a .net dll and use it in your vba project.
LVL 38

Accepted Solution

PatHartman earned 250 total points
ID: 40336553
VBA is the embedded language of Access.  There is no way to use directly.  You would have to write the code and create a .com wrapper class and call the .com wrapper from VBA.  If you were performing something completely isolated like converting data from another application or screen scraping to get data, you might be able to justify the complexity but not for something that would normally be done with VBA.

If you are asking this question, you probably shouldn't be using Access as a FE at all.  Just use  It will be a whole lot easier than trying to merge the two incompatible technologies.  You'll be a whole lot happier and who ever has to follow in your footsteps will be also.  No VBA programmer will thank you for that kind of kludge.  It will take a programmer to sort it out and your client will curse your name for costing him so much money.
LVL 13

Expert Comment

by:Russell Fox
ID: 40336562
You can create an assembly (dll) file, register it in Access, and then call it like other native functions. Here's an overview of programming with Access.
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

LVL 11

Expert Comment

ID: 40336565
You should also probably try do away with access entirely.  MS SQL Server desktop edition is free, and a lot more powerful than access.  Unless there is a lot of access forms etc. you should really think about getting rid of Access.  There are also extra license fees associated with Access, so in the long run you will be saving your client money.
LVL 38

Expert Comment

ID: 40336596
You should also probably try do away with access entirely.  MS SQL Server desktop edition is free, and a lot more powerful than access.
You are confusing Jet/ACE with Access.  Access is the rapid application development platform that is used to create applications.  Jet and ACE are the relational databases that are used to hold data.  You can compare Jet and ACE to MS SQL Server but NOT Access.  SQL Server cannot replace Access as a development platform.  Access requires Jet or ACE as a container to hold its own objects but Access can be used as a front end for any RDBMS that is ODBC compliant so Jet and ACE are not required to hold data for an application.
LVL 11

Expert Comment

ID: 40336620
@Pat, I'm not confusing anything
Unless there is a lot of access forms etc. you should really think about getting rid of Access.
We have a legacy system built on Access forms which I am busy rewriting into MVC.
LVL 58
ID: 40336656

   I agree with Pat; SQL Server is not a direct replacement for Access.  It's a direct replacement for JET/ACE, which is part of Access.

  Now if your comment had said "MS SQL Server desktop edition is free, is a lot more powerful than JET/ACE,  and you can re-write the front end forms and reports in MVC or some other tool", then it would have been more of an apples to apples comparison.

 Switching to SQL Server alone doesn't get rid of Access, which is what Pat was pointing out and trying to clarify.  Also depending on your needs, simply switching out SQL for JET/ACE may be more than enough for some.


Author Comment

by:Gary Samuels
ID: 40336690
I built this application many years ago as a simple access database project. As time has gone by I changed it so that we use MSSQL as the backend database and Access as the front end user interface. There are now dozens of forms and reports based on countless queries. Everything has been coded using VBA. Rebuilding it as a application is not something I have the time to do.

I want to get rid of our in-house servers and move everything to the cloud. I've established an Azure account, copied the SQL server to Azure SQL and copied the Access database to a Virtual Windows 8.1 machine. I've also setup Azure Storage to save images and documents that may be displayed within the Access forms.

My current dilemma is building a connection between Access and Azure Storage. There are many examples of how to do this in or C+ but no one has a solution of how to do this using VBA. I've been looking for someone to hire, someone to build a .dll holding the connection strings to Azure Storage so I can reference it in Access but no one appears to be interested.
LVL 58
ID: 40336745
<<My current dilemma is building a connection between Access and Azure Storage.>>

 Azure is really SQL Server and as such, you can connect to it with ODBC.  I would setup a DSN connection first, test that and then work with it in Access either with the DSN or DSN-less.

The only tricky part is the connection to the server, which will look something like this:,1433

outside of that, it's straight ODBC...

LVL 58
ID: 40336759
FWIW, there are some notes here that you might want to read through:

Guidelines for Connecting to Azure SQL Database

  But like I said, just straight ODBC; no need for .Net

 I would also add however that your probably not going to like it.  Your going from an on premise server to an off-premise one.  Because of that, your expectations and app design need to be fundamentally different.

 It's not a situation where you can just swap out MSSQL for Azure and call it done.  

 Everything and anything will need to be pushed server side, and gone will be the days when you simply bind a form to an entire table, but rather present summarized data and then let users drill down to where then need to be.  Basically in a nut shell, you need to have a focus on minimizing data going across the wire far more so than you've done up till now.

LVL 38

Expert Comment

ID: 40336965
I want to get rid of our in-house servers and move everything to the cloud.
Personally, I would think long and hard about that move.  As a business owner, it would scare me to death to have my data so out of my control.  Azure in particular is out of your control if you are not running it on your own server.   You'll be paying the provider extra just to send you backups because even the high-priced plans only include 14 day retention.  You'll have more options and more providers and more control if you stick with SQL server.  The cloud is best used when you need to share files with people not on your LAN.  I've been backing up my own data to the cloud for 10 years and although I am somewhat concerned about all the hackers out there, I accept the risk in order to have a good backup methodology that I don't have to worry about.

As Jim already mentioned, you will probably be disappointed in the responsiveness of the Access app when connected to an Azure database.  Even the slowest LAN is at least 10 times faster than the internet and unless your Access app is optimized for client/server it will be bringing down way too much data from the server.  And even if it is optimized correctly to never bind forms directly to tables and to use queries with criteria instead, you will still find it sluggish due to the inherent speed difference between a LAN and the internet.  Think about it, when you connect via the internet you are vying for bandwidth with all those people out there downloading movies and music and playing games and they are sucking the life out of the internet.  Third world countries have better internet speed than most of the US because their infrastructure is newer.  

The best solution I have found for remotely sharing Access applications is Citrix.  The app doesn't require any changes.  You can run the Citrix server in house (which it sounds like you don't want) or you can find a service bureau to manage a Citrix server for you.  The reason that Citrix gives such optimal performance is because the Access app runs on the server and is local to its database.  The Citrix server sends pictures of the desktop to the remote user and receives keystrokes and mouse coordinates so the data transfer is very small.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question