Question What happens to VBA in Acc2010 Web Database? (1 Viewer)

[beachldy]

When a regular Access application is converted to a Web database in Access 2010, what happens to the code (vba)?


I've heard that with Access 2010 and Sharepoint 2010, that a developer can create Web databases and have them used in Sharepoint. So if I have an Access 2007 database, and I go to Access 2010 and create a new WEB database, what happens to the VBA when I import in the the Access 2007 objects? Or what if I just convert the 2007 version to a regular 2010 version and THEN try and send it to Sharepoint? I haven't really seen what happens to the actual code in the webcasts I've looked at.

 

[Banana]

If you were to import in old objects from an existing Access database into a new web database, you'd succeed and it'd work just as it has before. But it won't be available on a web browser -- only in the Access environment. This is known as "hybrid application" to support cases where it is desirable to have some web objects to support a function while having a full rich client applications for as example, internal users.

In web objects themselves, VBA is not available and you would need to use macros.

HTH.

 

[beachldy]

How are functions and subs called in an Access 2010 WEB database then? Or SQL procedures that were originally called using pass-through queries? In macros, "RUN CODE" could normally be used to run a function...but without VBA, that doesn't seem possible...Or can I program in JS or VB Script within Access 2010? There is tons of VBA code in the original app behind reports, forms and many modules, like certain string function manipulations, processes, etc.

So how is coding done with this Web database? Sorry for the questions, but I only saw a few webcasts today but nobody mentions CODING, functions, and subs.

 

[Banana]

For all practical purposes, VBA are not available in a web database. To do any kind of coding, you would use macros (which I should hasten to add that they're quite different from what we know about them in 2003 or 2007).

Certain macros such as RunCode wouldn't be available in a Web Macros, though there is a IsClient() function that enables one to branch a web macro out into a regular macro or VBA but _only_ when we're running Access, not in a web browser.

Currently, there is no access to client-code inside the Access web, though two things are possible right now:

1) Use Visual Studio to manipulate the Sharepoint Object Model. One would access the model by triggering for instance, list's event.

2) Use Access 2010's new web browser control and point it to a Sharepoint web part that can then execute a custom ASP.NET code.

But back inside Access environment, we would use macros.

Note that with 2010, we have 3 classes of macros:

1) Data macros... That's basically our stored procedures & triggers. The cool thing is that they're also available even in regular Access environment so for many VBA workarounds using form events is no longer needed - we can use those and thus associate the business logic with the tables instead of forms which had their own problems (e.g. ensuring users go through the form and not by some other avenues)

2) UI Macros... When published, those are what get translated into Javascript and thus run client-side in a web browser.

3) Client Macros... Basically same as #2 but run only in Access environment and isn't available in Web browser.

Also, you should be aware that the web objects are quite different from client objects. For example, web reports have NO events at all and no subreports. Web form has reduced numbers of events available. Thus, I would not expect a seamless or even easy conversion. At this stage, since web database is basically "version 1.0", the most probable use of web database is well, of course, hybrid application where some content are made available on web while the bulk of application continue to function in native Access environment.

When you think about it, it's probably what most clients are going to do - they usually don't have wad of cash laying around to pay for overnight conversion of a complex application so they would want to do it in staged process. Also, as pointed out, conversion would do some disservice to the new functionalities - we've for long time used form's BeforeUpdate event to do validation of our data but that should now be done in the Before Change event attached to the table, enabling us to code validation logic only in one place and thus not for every form's BeforeUpdate that happens to use the same table but in different contexts.

With that in mind, you would want to take a look at Access team blog which lists few good blogs on web macros/databases. They also list a company offering conversion service to convert your database to web database.

HTH.

 

[beachldy]

Thank you, Banana, for clarifying more in detail. Hmmm, seems like I'd be better of using the conversion just for getting the base object designs, then using Visual Studio for all programming. Is that even possible and THEN exporting to Sharepoint?

Another question, what does this mean "hybrid application where some content are made available on web while the bulk of application continue to function in native Access environment"? Are you implying that the App would still have to run as an Access Application , while only the simple parts would be available through the Web Database/Sharepoint Web? Is there any way to get the full Access hybrid app to run as an Access Application within Sharepoint, such as a Shell opening Access 2010? Right now, everyone runs the app on Terminal Server as a shared app.

JS conversion for macros makes sense, hopefully VS or VB will be added in the next version with coding conversion from vba....though that would be quite a feat. Maybe coding editors could be worked in(or are we making .NET functionality within Access now, lol?) I do like the "before change" event on tables, and that is familiar since I've worked on a few web apps.

 

[beachldy]

Another quick question....Our current Access database is using tables from SQL SERVER. Does the conversion handle that too?

 

[Banana]

Well, if you really need nitty-gritty customization, it'd be best done in an full-on ASP.NET application at this stage.

Here's how it works in Sharepoint. Instead of creating web pages scattered in a directory somewhere on the server, we store all web content as a record in SQL Server which Sharepoint will then retrieve and build dynamically in a response to web request.

So when we build an Access Services database and publish it, the process convert the web objects such as forms into HTML and XML, UI macros into Javascript and upload it to Sharepoint which then store it in SQL Server. For web tables, those get converted into Sharepoint lists and for all practical purposes are same as any old Sharepoint lists (note that they added improvements to SP lists such as referential integrity support among other things).

With a regular Sharepoint site, it is possible to use Sharepoint Designer to customize the web part. However at this point, Access Services' sites will disable and prohibit Sharepoint Designer from customizing the site since it's possible to customize in such way that would break Access Services. With Visual Studio, we can build new web parts/content and add it but I don't think we can customize an existing Sharepoint site (I could be wrong here, though).

So all in all, even if we managed to get into SQL Server and locate the records then edit the HTML and JS there, it's more likely that any changes and thus synchronization would undo our changes. Then there's the uncertainty that our changes won't break. I've already mentioned what we can do- attach an event receiver to the list or use webbrowser control to point to a custom web part if we need to enter ASP.NET world from Access Services. Because web tables are now lists, they're just like any other Sharepoint lists and thus can be interacted with via Sharepoint object model. Interacting with the Access Services site directly is more difficult - we probably are going to be limited to what web browser control can show based on what we code the web part to do.

Regarding the hybrid question... we need to keep in mind what we can use Sharepoint and this can get a bit confusing following what they mean by "publishing". In 2007 we could "publish" a database, but what they meant by publishing in 2007 was "move the database into a document library in Sharepoint so anyone could then download the copy". In 2010, the word "publish" now means "let's make a web database" but the original functionality hasn't went away. We still can upload the full copy of database to a document library and if a client has Access installed on their computer, Sharepoint provides (and if permission is given) a button "Open In Access" in which first time they'd download the full copy and open the Access and thus get everything they would have. They can use both web and client objects with VBA in there. Furthermore note that any subsequent download would be now differential rather than complete which makes a good case for using Sharepoint as a means of distributing and synchronizing changes made to the front-end application. Also, Sharepoint provides the capability to work off-line so that also replaces the old replication while simplifying the effort required to properly set up replication. That way, client can continue to work, add/edit data and synchronize all changes once back in contact with Sharepoint server.

As for lack of code editors... I really, really, really hope they do that. But If it's going to happen the best thing people can do is to tell it to Access team (they have a contact page on their blog). That won't guarantee they'll do it but that is better than saying nothing then seeing they didn't bother because they thought we didn't need the feature.


HTH.

 

[dfenton]

I think you're wanting Access web apps to be something they are not.

An Access web app has a limited set of functionality because web applications have a limited set of functionality. Arbitrary VBA code can't be guaranteed to be convertible to something that can run in a web browser, so it can't be included. The new macros (which provide most of the fundamental features of any programming language) are a structure way of creating subroutines that are limited to those commands and features Access and Sharepoint will be able to understand and convert to a web application.

As to SQL Server, I believe those who've been working with it already have said that if you publish an Access app to Sharepoint that uses linked tables to SQL Server, it will continue to use that data source (though likely not via ODBC, of course). But I could be completely wrong on that -- I'm working from what people like Albert Kallal have posted and from what I've read elsewhere.

 

[Banana]

As to SQL Server, I believe those who've been working with it already have said that if you publish an Access app to Sharepoint that uses linked tables to SQL Server, it will continue to use that data source (though likely not via ODBC, of course). But I could be completely wrong on that -- I'm working from what people like Albert Kallal have posted and from what I've read elsewhere.

Well, technically, linked tables would be available in an Access Services database but as a client object rather than web object. Right now there is no facility for interacting with external data inside an Access Services in the browser -- we need to use Access client to do that. Sharepoint itself supports "Business Data Connectivity" which basically is the ODBC in Sharepoint's world but this is not available to Access Services at this point. To use such thing, one would have to use regular Sharepoint site.

Access team has gone on the record on their blog that this is a shortcoming they intend to address in next version.

 

[beachldy]

Thank you so much for clarifying the issues in question! That sure gives a great insight into both capabilities AND limitations.

If a miracle existed, wouldn't it be nice to have Access apps converted to .NET on the fly? Looks like I'll have to set up a test environment to see where the ASP.NET can be used and/or web parts. And then, the next thing will be security and filtering by user groups through Sharepoint. Currently, we use security groups and SQL db roles.

 

[Banana]

Well, when Access Services database is published, we'd be now using Sharepoint security, so there's that.

As for conversion to .NET... it certainly would make everything easier, and at least I can say that Access Services itself is in fact a .NET assembly, so there's definitely movement toward .NET, if not on the surface but at least under the hood.

I don't think, however, they've made any progress with the client application or ACE itself - it's still largely a COM application. At least I don't know about any progress in that area except for Interop assemblies being available.

 

[dfenton]

If a miracle existed, wouldn't it be nice to have Access apps converted to .NET on the fly?

I don't see why. .NET is not some magical solution to every problem.

That said, an Access web app delivered through Sharepoint Access Services is, in fact, converted to .NET XAML forms (or whatever object XAML represents), according to a post today by Albert Kallal in another forum. So, you've already got on-the-fly converion to a form of .NET, just a form that has to render in a browser.

Frankly, I can't see any advantage of avoiding the browser and running it in the .NET runtime, which seems remarkably fragile to me (it's constantly breaking and needing patching, and I have to have multiple versions installed to support all the apps targetted to specific versions).

 

[beachldy]

Dfenton, I mention .NET because it seems every time there's a change in Msft products, they point more and more towards .Net for solutions (sic, the Access 2010 change). How long before Access gets phased out (sometimes it seems it may be coming)? If it's to be pushed to NET, then let a conversion be provided where modules can be stored to be "fixed" manually where asp code conversion fails.

 

[dfenton]

I don't know what the future of Access is in terms of programmability, but it seems pretty clear to me that Microsoft is currently putting its resources into the macro builder so that limited scriptability is possible. That allows them to convert the macros to something that is guaranteed to work on a web page.

This is a good thing.

However, I fear for the loss of the open-ended programmability we've had with VBA. Keep in mind that .NET is not as open-ended as COM. Well, you can use COM in .NET, but you can't do it with managed code because of the security holes it opens up.

My hope is that .NET will be somehow offered as an option in Access sometimes in the next couple of versions, but it won't be as completely flexible as VBA has been.

But I have no inside information at all -- I'm just guessing.

The least likely thing, seems to me, is the removal of any free scripting language with no replacement (i.e., drop VBA and replace it with nothing but the beefed-up macros). The question is just what we get in place of VBA/COM, and I don't know enough to speculate usefully on the realistic possibilities there.