Comfortable / VB / VBA

[mission2java_78]

Been looking to create my next tool using SQL Server as the back end. But I want some advice. Generally would it be safe to continue using MS Access as the front end tool rather than VB? The reason I ask is im more comfortable with VBA rather than VB. Is the only advantage I have using VB is getting a .exe out of my application or is there a lot more to this? Also table wise...do yuo guys go right into creating the tables in SQL Server and linking em..or do you create them in access and then when you're done move em over or recreate them in SQL Server...just need advice.
Jon

 

[bdp]

Yes it is safe to continue using Access for your front-end. The main difference developing in the VB environment is that it is more robust than the Access environment. VBA and VB are interchangeable. VB is an environment and VBA is the language used when programming VB, MS Office or other thrid-party software. VB is a complete RAD (rapid application development) environment that features UI components and relies on VBA as its programming language. The majour differences between the two environments are (VB and Access):
1. VB programs can be compiled into executables and run in their own process space (don't need to have VB installed on all the computers using the application)
2. VB apps can be compiled into ActiveX components and used in other applications
3. VB apps can be compiled into native code exes, whereas VBA applications are always interpreted.
4. VBA is generally used to "enhance" the host application whereas VB creates a standalone app.

I have created numerous applications in both VB and Access environments. If the program is going to be quite complicated and process heavy with many users, I generally use VB since the environment is much more stable. Also, there are more controls, properties, methods and events exposed in the VB environment which allows me to generally create complex applications faster. You also don't have to make the "work-arounds" in the VB environment like you often need to Access. You also don't have to worry about which version and service packs the clients are running, which is a big bonus. VB is also much faster running code than Access. However, if it is a smaller app and not too process heavy, I like to use Access because typically, you can create a smaller application faster and it also has it's own database. Another bonus about Access is that it has one of the best reporting tools available.

If you haven't had any majour problems or limitations with your front-end, I would just keep it in Access to save you some time.

If I am creating the data-tier using SQL, I always create my tables directly using SQL. I tried once to move them from Access, but it was a huge mess. It may be better now, but I make it a rule to create them in SQL.

 

[mission2java_78]

Sounds good...if you created the tables straight from SQL then did you just link them as ODBC source? Also did you have to work on Security to get this working...

 

[mbailey]

Jon,

If you're developing a new app with SQL server as a backend, check out Access Data Projects (.adp), rather than using ODBC through a .mdb.

The .adp file is basically all the access front end components (forms, reports, macros, and modules) and a connection to the SQL Server. It will allow you to develop your SQL tables and queries (views, stored procedures and functions) from within the Access environment (like you are used to doing).

There is a book on the subject that I found extremely valuable; "Microcoft Access Developer's Guide to SQL Server", published by SAMS. Hope I'm not breaking any rules plugging this book, but it will help you out a lot.

Marty

 

[mission2java_78]

What's better? From what I've heard from Pat she generally just links to the table and keeps the .mdb format.

Pat?

 

[mbailey]

I personally like the .adp if starting from scratch. It allows you to do all your server development from within the access environment, and I believe that it is quicker than ODBC, as you are working directly with SQL Server through its OLE DB provider. The forms in an .adp use the ADO cursor engine to manage the form's recordset which consumes very little server resources.

But there are a lot of advantages to the .mdb, especially if you are going to gather data from multiple sources. ODBC is the quickest, most painless way to upsize an existing access database. Also, with this format, you still have local data storage and queries, which can be a big plus for client side processing.

I, too, am interested to see if Pat has anything to add.

Marty