using vb.net in access 2013
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Russell Fox
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.
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.
@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.
@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.
@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.
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.
ASKER
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.
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.
<<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:servernamefroma
outside of that, it's straight ODBC...
Jim.
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:servernamefroma
outside of that, it's straight ODBC...
Jim.
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.
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.
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.