VBA or VB.net with MS Access

[lithium1976]

I have been using Access 2003 to design a few databases.

I am starting (early stages) to learn VBA to allow me to perform more functions in the database.

My question is -

Can i use VB.net instead of VBA in access? I am aware that VB6 is being phased out, and how does this affect VBA in the future? Is VB.net used with Access database design?

Sorry but im just confused and need it clearing up! Sorry if it sounds like a daft question.

This is my first post

[Hack]

Welcome to the forums.

VB.NET most assurdedly can be used with Access or just about any other database of your choice.

VBA is unaffected by VB6 as VBA is a scripting tool (basically a subset of VB) that is built directly into Office Apps.

[RhinoBull]

Can i use VB.net instead of VBA in access?

No.

Is VB.net used with Access database design?

As far as building front end - YES.

[lithium1976]

Thanyou for your reply!

So can / does Access 2007 (and 2003) work with VB.net from within Access or does it run as a stand alone program?

In access 2003 I have dabbled with its VBA as VBA runs inside Access. Does VB.net run inside Access?

Im still confused - should I be looking into using VBA or VB.net with Access?

[lithium1976]

As far as building front end - YES.[/QUOTE]

Doesnt VBA allow this also?

[Tom Sawyer]

It depends what you want to do with it.

If Access is just being used as a database, I'd suggest using VB.NET as a front end, as it allows for far more options.

If you're using Access for more than that ... honestly, I'd consider redesigning your application so that you don't. I haven't used Access for the last few versions, so it may have gotten better, but from what I remember, pretty much everything about it sucked and could be done better with a VB front end.

[lithium1976]

In the past I have used VBA in Access to help the functionality of my forms -eg for searching in a text box for records then displaying the record, selecting records to view in a new form from a list box of records, copying tables for archiving and then deleting them from the original table, showing messages such as "are you sure you want to continue?" etc etc

Can I design the front end in VB.net? Is there any reason to design the examples above in vb.net rather VBA in Access? If so should all forms be designed in VB.net and just leave tables and queries in Access?

[Hack]

Can I design the front end in VB.net?

Yes

Is there any reason to design the examples above in vb.net rather VBA in Access?

An entire galaxy of programming features not available in VBA.

If so should all forms be designed in VB.net and just leave tables and queries in Access?

Do everything from within .NET - use Access for nothing more than storing your data.

[lithium1976]

YesAn entire galaxy of programming features not available in VBA.Do everything from within .NET - use Access for nothing more than storing your data.

Thanks thats great

[Hack]

That includes writing and running queries. Do that from .NET as well (and use parametized queries not in-line SQL)

[RhinoBull]

Although MS Access is a nice (nothing more than that) db engine - it's still a desktop sort of database and it is not free
We now have very robust MS SQL Express, Oracle Express that will make your life (as developer) much much easier and they are free.
And if you ever need to migrate to full version of each product - it will be a breeze - migrate database from MS Access to any commercial enterprise database is always a headach.

Good luck.

[lithium1976]

I have two Access databases which I want to learn vb for the front end and queries, and its probably going to be vb.net which I end up trying to learn, but do you think I should move away from Access then and try SQL Express?

[RhinoBull]

... do you think I should move away from Access then and try SQL Express?

I most certainly think so - if you learn how to work with MS SQL Server or say Oracle then using MS Access will be "no-brainer" at all.

[Shaggy Hiker]

I don't quite agree with RB on this. Access is still a VERY popular and widespread DB, and will continue to be so as long as it is being bundled with MS Office. If you have DBs in Access, and you will only have one or two concurrent users, then Access is fine. As for the cost, if you are using VB, you don't actually need Access to be installed as a separate program on the target computer. You can create the DB, create the tables, etc. all from within VB.NET, and when you install such a program, the necessary components will get installed, as well.

Ultimately, if you write your program in VB, and you do a thorough job of it, there is never a reason to look at the DB directly whether using Access or other. Access is only a generic front-end on the data. If your front-end works sufficiently, why would anybody go to the generic instead?

There are certainly more robust DB options out there, and there are better ones, but for now, I still think Access has a robust future, and shouldn't be abandoned without cause. It sounds like you have working, existing databases in Access. If you are building a .NET front-end, then there might well be a good reason to switch....or not. I wouldn't do it without good reason, though.

[lithium1976]

Purely out of interest and as a beginner to VB.net and SQL Server, I have only ever designed databases in Access so what program would I use to design database tables?

VB.net for front end, SQL server for Queries.. which program for tables?

[Hack]

Design your SQL Server database with the same guidelines you used in designing your Access database.

Do every, including queries (parametized of course) in VB.NET

The additional advantage, however, that you have with SQL Server is that, instead of parametzed queries in .NET, you could write Stored Procedures for this task.

[si_the_geek]

If you mean which program do you use to actually create the table that you have designed, it would be Management Studio (for SQL Server 2005+) or Enterprise Manager (for SQL Server 2000 or earlier).

[RhinoBull]

... Access is still a VERY popular and widespread DB...

Amongst Who? Amateurs and those who writes mostly small apps and never enterprise? You can have an ordinary office clerk creating database in MS Access in a matter of minutes - of course it is widespread...
It lacks some important functionalities such as stored procedures, triggers, etc. It has limited max space, it has different types...
It's a real pain to get it to work over the internet... List can go on and on...
Other than for learning purposes there is no reason to develop against MS Access anymore.

[Shaggy Hiker]

Amongst Who? Amateurs and those who writes mostly small apps and never enterprise? You can have an ordinary office clerk creating database in MS Access in a matter of minutes - of course it is widespread...
It lacks some important functionalities such as stored procedures, triggers, etc. It has limited max space, it has different types...
It's a real pain to get it to work over the internet... List can go on and on...
Other than for learning purposes there is no reason to develop against MS Access anymore.

Despite the limitations, it is found all over everywhere for the simple reason that it is bundled onto so many computers. If you are writing enterprise solutions, then that certainly wouldn't be the database of choice. However, I work for a state agency with indifferent computer knowledge. The more technically proficient sections use either SQL Server or Oracle based on reasons that rarely have any resemblance to logic, regardless of whether you like the final result. However, the technically proficient groups don't play well with the more aggressive groups, so the people who are trying to get a solution up and running quickly, have numerous Access databases here and there, while the technical groups try to slowly absorb those DBs into a better framework.

The agile groups use Access, the proficient groups slowly absorb the Access databases into SQL Server. The dance goes on with little grace and much sound and fury. I've been on both sides, and understand both sides fairly well. Each is doing what is best as they see it, and each is right. Some kind of ivory tower denunciation of Access needs to be able to offer up an alternative that is:
1) Easy to use for non-technically savy individuals.
2) Readily available.
3) Has widespread support within the knowledge base of non-technical people.

Access does that, no other DB does. I expect that in business, there are different considerations.

[RhinoBull]

Access does that, no other DB does.

All I can say is "Happy Access development", SH.

Regards.

[si_the_geek]

Some kind of ivory tower denunciation of Access needs to be able to offer up an alternative that is:
1) Easy to use for non-technically savy individuals.
2) Readily available.
3) Has widespread support within the knowledge base of non-technical people.

Access does that, no other DB does.

That's a fair point.. except that 2) is actually a vote against Access now, as Express/Compact editions can easily be downloaded for free.

Also, purely in terms of a data store 1) is debatable, as it only takes a couple of extra clicks to select the database to work with (but security can be a little more complex).

I expect that in business, there are different considerations.

Indeed.. and until recently Access was the best option in a large variety of situations (and although they were the bulk of the cases, certainly not limited to small apps/non-enterprise).

While features like SP's/triggers/better security/etc are very useful, they aren't essential for every system - sometimes other factors are more important (such as the ability to run on a laptop that may not be connect to a network).

Thankfully now the Express and Compact editions of SQL Server have been released (and equivalents from Oracle etc), so there are much more stable and functional alternatives.. which happen to free/low-cost, and easier to upgrade/synchronise if the system requires it.

[Shaggy Hiker]

This is mostly a debate by technical people about the technical merits of different data storage system. I deal with a bunch of biologists who are not very well informed about different types of data storage options, and have no interest in becoming informed about them. In most cases, the choice is Excel vs Access. They may have heard about things like SQL Server, but they understand things like that to be in a different realm of understanding other than the scientific arena that they are interested in. They are quite willing to let a technical type explain the benefits of using one type of DB over another, and have no issues with us making that decisison....as long as we are providing the support. Since there are few of us, only a few projects really get much support, so we end up with individuals collecting and storing data based on whatever they happen to be familiar with.

As an example, our genetics lab decided they needed a database. Now, they are very technically savy with equipment and jargon that most people on this forum wouldn't even recognize, but computers are of little interest to them as anything other than tools. They used what was at hand, which was the software installed on the systems, and the knowledge that they had, which was not at all related to programming, computers, or software. The result: They have a functional database that works for their samples, but doesn't integrate well with anything else in the department. This would be an issue, except that nobody can spare the time to fix it right now. We will improve their database, but maybe not within the next two years or so. While others debate what would be the BEST way to get the job done, they got it working with the knowledge and tools that they had. It's hard to argue with that. Sure, you could say that they could have used better tools and done a better job, but you have to realize that they are already insanely overbooked already (any capable genetics lab will have more to do than they have time for these days). How much time would you suggest that they take away from their overbooked schedule to learn new technology and implement it without any support?

It's this group that will keep using Access. MS has made bundle deals with lots of agencies including our own (and the feds, too, I think). What training we have for field staff is on the software that comes on all of our computers. It sure isn't SQL Server. Access will still be a significant player until some other piece of software becomes common to all systems inside an organization like this. Schools teach Access and Excel in intro classes that can be taken by scientists getting a little computer knowledge. Once those intro classes that pad out a transcript are classes in SQL Server, Express, or anything like that, then Access will slip. Until then, it will remain strong.

[Shaggy Hiker]

All I can say is "Happy Access development", SH.

Regards.

Thanks, I'm actually using Access as a database for my robot project at the moment. After reading the pros and cons, I don't see a single argument for switching that applies. That's not to say that Access is the best, but none of the advantages mentioned for other DBs makes any difference in this case.

Actually, it might be the case that stored procedures could become useful just because of their potential for performance boosts, but nothing I have written has been complex enough (for the robot code) that a performance boost was likely.

[si_the_geek]

That's a good expansion on your previous points 1 & 3, and those are valid points for relatively non-technical people.

As to your robot project, it's possible that ability to take hot backups would be useful, or perhaps something else.. but then again, perhaps not, as the time saved by using what you already know could be a bigger advantage.

[Shaggy Hiker]

Actually, except for a potentially large amount of data, the database behind the robot will be the strangest, yet the simplest, one that I have ever dealt with. It is pseudo-relational. Items in one table will eventually become related to items in another table, but not at first, and the relationships may change over time....or not. It's a fun project.

One of the other DBs might well be a better choice, because Access can have issues when a network connection gets dropped. I don't really anticipate that happening, but it might, since much of the connection is wireless. Other than that, I could probably write the whole thing to use Excel for as simple as it is.

[RhinoBull]

Shaggy,

are you aware of Microsoft Robotics Studio? It's pretty cool and I'm going to invest some time investigating it.

[Shaggy Hiker]

Yes, I have heard of it...about a month too late. There is a really nice platform that would work well with it, but by the time I found out about it I had sunk so much money into a different platform that I couldn't bear to switch away from it.

This company

http://www.roboticsconnection.com/

puts out a really nice piece of hardware with a collection of .NET classes that integrate well with it. They hit the market just a bit too late for me. I did end up buying the chassis because the track system and motors are really good, whereas the system I had was throwing tracks whenever it turned on carpet (not enough track tention, and no way to fix it).

My next bot will be using the whole system from them, and I'll be looking at the robotics studio at that time. I just can't justify dumping my investment in the current brain board. They also didn't originally have all the sensor pieces I needed.