Access and Azure, existing database

Hello Everyone,

I have a program/database that was originally stored locally for a client and ran completely in Access 2010.  It was then put up on the cloud (azure) with the help of an outside source.  My problem is this, when it was completely in Access 2010 I could work with it because I know Access and VBA.  Now that it has been moved to the cloud I have to do queries and such in SQL, he converted much of my work to stored procedures, and incorporated modules.

For most of you these things would seem like a great step, but for me, it is like another language and it has really impacted what I can do when I modify the program (I feel like I am trying to debug French when I only understand English).

My question is:  Is it possible to move only the tables onto the cloud and then give every user the Access program (runtime version) which would contain everything BUT the tables?  Basically I want to be able to use Access to create queries, reports and such without having to really know SQL.  I know this would probably be a step backwards, but I was wondering if it could work.  This way I could understand my own program again.

Thanks
Andy
alevin16Asked:
Who is Participating?
 
Nick67Commented:
I never used modules.
Ok, so you meant modules in Access.
Sometime you have code that is general to the whole app, and not tied to a particular form or report.
That code goes into modules.  Nothing scary.  My app can send email from many different procedures.
The code for it is in a module so every object can access it at need.
It's just VBA, no biggie

Class modules are a step farther out.  They are still VBA code, though
A form is an object.  So is a report, or a Recordset or anything else you declare with Dim Something as AThing
Each object is an instantiation of a class.
Class modules allow you to create your own objects
We do things like
Dim rs as Recordset
Recordset is a built-in class that we can instantiate and use.
It has properties and methods we can use.

You can build your own classes and use them
That's object-oriented programming or OOP.
Takes a bit to wrap your head around.
And depending on the zealotry of the programmer, you can move from useful abstraction to obfuscation in a hurry :(

I do want to try editing the original version to access the tables on Azure
Sure.
Look at your new version and the connection string for the linked tables.
You'll want to use the Linked Table Manager to point the old version at those new links.
Or use the TableDefs collection in VBA
This worked before I gave up on Azure, but it may be a bit dated.
Substitute the items in bold for appropriate values.

Function Refresh_Table_Link()
On Error GoTo myerr
Dim TD As TableDef
Dim linkstring As String
Dim intSubStringLoc As Integer
For Each TD In CurrentDb.TableDefs
    If Len(TD.Connect) > 0 Then
        intSubStringLoc = InStr(TD.Connect, "DATABASE=MyDataBaseName")
        If intSubStringLoc > 0 Then
                linkstring = "ODBC;Description=TI_Azure;DRIVER=SQL Server Native Client 10.0;SERVER=TheNameOfYourAzureServer.database.windows.net;UID=YourUserName@TheNameOfYourAzureServer;PWD=TheRightPassword;APP=Microsoft Office 2003;WSID=MyComputerName;DATABASE=MyDataBaseName;"
                If TD.Connect <> linkstring Then
                    TD.Connect = linkstring
                End If
            TD.RefreshLink
        End If
    End If
Next
Exit Function
0
 
Kelvin SparksCommented:
Yes you could BUT, performance may take a hit.

T-SQL - (the language of sql server) is not too much difference from VBA, and for views (select queries) they're not too different.

Is worth taking a little time to learn a bit.

Kelvin
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How do you launch the database now? Do you navigate to a web site, etc etc - or do you launch it from a shortcut on your desktop?

Azure is only a SQL database. If you're running the Access FrontEnd on your local machine, you can still use that to create forms/reports and such, and you should be able to create queries using Access. It's generally better to create your queries and such as Stored Procs, since that moves the processing to the server - but there's no requirement that you do this.

If the outside source converted this to a web app, then all bets are off.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
alevin16Author Commented:
Hey All

Thanks for the quick responses.  To answer Kelvin, I am trying to learn T-SQL but the Update/Append queries seem a little confusing for me.

To answer Scott, luckily no, it has not been converted to a web app just yet.  Each person has a shortcut on their desktop that opens Access Runtime and the program.  I was pretty sure that if I did the queries locally it would probably be the slow way, but I would love to experiment to see how bad it would be.  

It sounds like you are saying it would be possible to have just the tables up on Azure with everything else local?

Thanks!
0
 
Nick67Commented:
It sounds like you are saying it would be possible to have just the tables up on Azure with everything else local?
In many ways, it still is that way, even now.

Your forms, reports and VBA are all local.
The data is up in Azure.
Your consultant converted much of my work to stored procedures
He likely did that for performance reasons.
With Azure you REALLY want to ensure that Access only gets served the minimum amount of data possible.
incorporated modules.
What might that mean?

Is it possible to move only the tables onto the cloud and then give every user the Access program (runtime version) which would contain everything BUT the tables?  Basically I want to be able to use Access to create queries, reports and such without having to really know SQL.  I know this would probably be a step backwards, but I was wondering if it could work.

It could work, but my trials with it were both early and painful.
You need a good internet connection, with little latency, and adequate upload and download speed and data limits.
I lack, and cannot get, a connection that'll make it work.  6Mbps down and 500K up was painful.  I tried to get real-time sync going with an inhouse SQL Server and an Azure trial database of ~300 MB.  Attempting sync at that time brought the server to its knees.

The question is:  Is your app working well RIGHT NOW?  If the answer is Yes, then start to familiarize yourself with T-SQL.
Hopefully, your consultant commented his stored procedures and code.

If performance RIGHT NOW stinks, then you may want to look at SQL Server Express (free!) on a local server, and pull your data back out of Azure.

That's my thoughts, for what they're worth.

Nick67
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
There have been ... mixed results when using Azure with a local Access FE. As Nick mentions, if your performance is okay right now, then you should be okay. Just keep in mind that since you're dealing with an internet connection, with the inherent instability of that connection, you may notice performance blips - times when the performance is very good, and times when the performance is not so good.

In general the more you hand off to the server, the better a setup like this will perform. It's not possible to do all things on the server, of course, and you'll certainly have some manner of local objects. Access is fairly intelligent when dealing with a SQL Server, and will generally do a good job of grabbing only the items needed.

Like Nick, I would very much encourage you to learn all you can about SQL Server. The more you can let the server do the better off you'll be.

Be sure your data structure is dead-on right. Oddities, like de-normalized tables, can cause significant delays.

Make sure your data is properly indexed. Any field that is used in a Join or Where clause should be indexed (and make sure those indexes carry over from the server for your linked tables).

Don't use table-type recordsets for Forms or Reports. Instead, create a SELECT statement as the Recordsource, or use a stored Query.

Of course none of these are hard-and-fast rules. You must sometimes experiment to insure you're doing the right things and make adjustments as you add data to the database. Indexes that work great today may not do so well in a year, after adding thousands of rows to a table, for example.
0
 
alevin16Author Commented:
Hello All

This is great information!  To answer Nick, about modules, I don't like to admit this but I never used them before.  When I created programs in Access I used Tables, Queries, Reports, and Forms with VBA code.  I never used modules.

Overall the program/database is working well, but when things go wrong I am finding that I do not really know how to fix it since he changed everything.  I am stumbling thru it and fixing most of it but I would love to have it back to the way it used to work so I could feel more confident.

I do want to try editing the original version to access the tables on Azure but to do everything else locally (specifically the queries).  I am going to look into the SQL Server Express, I had never heard of that before.

Thank you all again!  I am going to give this a shot.

Andy
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you've used VBA code in Forms, then you've used Modules - you just haven't used an stand-alone modules, I'd bet.

A Module can be as simple or as complex as you want it to be. Many people group certain "types" of functions into a common module. A good example would be Nick's "Email" module - you write the code in one place, and call that code when needed.

Another example might be a "Relink" module, where you'd have code that would be used when relinking your database from VBA, perhaps. This is code that might be called from many different locations

You mentioned that some else modified this database for you. If so, I'd contact that person and have them walk you through the changes that were made, and why. A good developer would adequately comment their code such that you (or someone else) could easily follow the logic used.

I do want to try editing the original version to access the tables on Azure but to do everything else locally (specifically the queries).  I am going to look into the SQL Server Express, I had never heard of that before.
SSE is a free version of MS SQL Server that can be installed on pretty much any machine. It has some limitations, but in general it's more than capable of handling anything a small-ish application can throw at it.
0
 
alevin16Author Commented:
Hello Scott

You are right I have never made a stand alone module.  I am going to look up some modules online to get a better idea as to how to make them.  That email module is something I could really use anyway.

I started looking into SSE and it looks to be perfect for what I need to do.

I cannot thank all you guys enough for all your help.  I am amazed I ever got any work done before the Internet (although I was mostly doing COBOL back then)

Andy
0
 
alevin16Author Commented:
Incredible comments!  They really went out of their way to help me on this with code examples and easy to understand logic!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.