Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

using vb.net in access 2013

Posted on 2014-09-22
11
Medium Priority
?
618 Views
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 VB.net? If so what do I need to do so I may use VB.net along with VBA.
0
Comment
Question by:Gary Samuels
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 28

Assisted Solution

by:MacroShadow
MacroShadow earned 1000 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.
0
 
LVL 40

Accepted Solution

by:
PatHartman earned 1000 total points
ID: 40336553
VBA is the embedded language of Access.  There is no way to use VB.net directly.  You would have to write the VB.net 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 VB.net.  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 VB.net programmer to sort it out and your client will curse your name for costing him so much money.
0
 
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.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 11

Expert Comment

by:LordWabbit
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.
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40336596
@LordWabbit,
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.
0
 
LVL 11

Expert Comment

by:LordWabbit
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.
0
 
LVL 58
ID: 40336656
@LordWabbit,

   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.

 Jim.
0
 

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 VB.net 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 VB.net 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.
0
 
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:

SERVER=tcp:servernamefromazure.database.windows.net,1433

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

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

Guidelines for Connecting to Azure SQL Database
http://msdn.microsoft.com/en-us/library/azure/ee336282.aspx

  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.

Jim.
0
 
LVL 40

Expert Comment

by:PatHartman
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

783 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