Hi All. I am new to this web site and find it very helpful. Keep up the good work.

I am also a Novice ( at best) when it comes to programming but really would like to master VB.
My question is, As I design my form, I would like to save the data that a user inputs into an Access Database (or for that matter it could even be an Excel Spreadsheet ) sequentially. Updating the database reguarly as the user inputs new data.

I know I need to lear ActiveX but as of now I do not know it. Is there a code given to a command button that will save the data in a given form to an Access Database or Excel spreadsheet? What do I need to do?

Thanks in advance for your guidance and support. - Mike

Hey. Thanks.

Well, there's a lot to explain here, and it's difficult to do so without screenshots. It's quicker if you visit a few sites on "connecting visual basic to a database." Check out the following. If you need any help, just post another question.

http://www.dutchthewiz.com/vb/db/
http://www.developerfusion.com/show/10/

In most cases, if you have a database such as SQL Server or Access, it's better to go through ODBC then access it directly (for Access). If you need help with that, let me know.

Thank you for the information.

I did find the web sites you provided helpful and will navigate through them to gain additional knowledge.

However, I would be seriously interested on connecting via ODBC. In a nut shell, I am basically trying to write a contact management application ( or address book ) and save the information to a database ( Excel or Access ). I read about the ODBC being the most reliable and simplest to implement but I may be wrong.

Again, Thanks for your help. Any guidance will be greatly appreciated. - - Mike D.

n1cole-
Just my 2 cents.... You seem to be working on Visual Basic 6. Don't. The term "web devlopment" in VB5 and VB6 came in the form of ActiveX controls, and, simply put, this is not the way to go. ActiveX controls are only compatable with one browser (IE), in one operating system (Windows). The newer version of Visual Basic, VB .NET, gets tied in with a web devlopment langauge, called ASP .NET. ASP.NET webpages are extremely easy to make, its a drag and drop interface to get buttons, textboxes, labels, etc on the webpage. Also, the actual coding of the form (like inserting into a access db) is very simple. And your in luck. Microsoft has an extremely good website www.asp.net (who else could own such a url), with more resources then you could ever immagen.

If you go to that website, you'll notice that there's a tab on the top that says "Web Matrix." Download that! Web Matrix is a FREE version of vb that only does web pages. This is a valuable resource!

My sugestion, grab a book on this. ASP.NET will not be going away any time soon. Its powerful, easy to develop, and most importantly its Microsoft. We might not all like M$, but there products stay around, for a long time.

If you can't get a book right away, www.asp.net will have plenty of resources for you to get started. Additionally, www.asp.net has a forum, for all your questions to be answered by ASP.NET programmers.

Hope this helped!

Well, if you want to connect to Access database through something like Visual Basic or ASP, it's good to setup an ODBC connection. First you have to establish a DSN, preferably a System DSN. You do this by the following:

System DSN

1. go to the control panel
2. click on "Data Sources (ODBC)" - If you have Win2k, go to Administrative tools, click on "Data Sources (ODBC)"
3. A window "ODBC DataSource Administrator" will show up. Click on the "System DSN" tab.
4. Click on the "Add" button. A new window that says "Create New Data Source" will pop up.
5. Because you said you wanted to use an Access database, then look for "Microsoft Access Driver (*.mdb)"
6. Click finish once selected.
7. A new window will pop. "ODBC Microsoft Access Setup"
8. Pick a name for "Data Source Name." Try not to use spaces in your name. Give a description if you want.
9. After selecting a name, click on the "Select..." in the Database part of the window. Now choose your Access file.
10. After you've selected the database, hit ok.

And that's all folks. Hit Ok in the "ODBC DataSource Administrator" window. There's more options in how you set it up, but I'll let you figure it out.

Theoretically you don't need to go through ODBC. You can access the database file directly. If you need code for that, or further explanation, let me know.

Here's how to connect to an Access database file directly, without setting a DSN:

If the database file is in the same directory as the ASP page:


<%
Set demoConn = Server.CreateObject("ADODB.Connection")
demoFilePath = Server.MapPath("yourdatabase.mdb")
demoConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & demoFilePath & ";"
%>

Where "yourdatabase.mdb" is the name of your Access database file.


If your database is in another directory:

<%
Set demoConn = Server.CreateObject("ADODB.Connection")
'this is the line to change
demoFilePath = "E:\database\yourdatabase.mdb"
demoConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & demoFilePath & ";"
%>

Where "E:\database\yourdatabase.mdb" is the obsolute path to your Access database.

I know I very rarely post in the programming section (and thank you Dan for continuously reminding me of that). But I do have a question for once.

Dan, you explained how one uses ODBC to connect to an Access database, and how to do it directly. Now my question ... What's the advantage of using ODBC?? Is it more powerful etc etc etc ?? Is it a Microsoft-only thingie?

What would be an alternate to ODBC?

It really depends on what database and operating system you're using. Microsoft tends to surround their products around ODBC which makes it easy for programming languages (especially VB, VC++, ASP) to access many databases including excel spreadsheets, even text files. Instead of you knowing how to interface with all these forms of databases, you communicate with only ODBC (from whatever language you're using) and ODBC handles the rest.

If you're using something like ASP, VB, VC++, classes and already-to-use tools exist within the language/IDE that makes connecting to a ODBC a snap. It's Microsoft, so it should already be included. On the other hand, if it's something like Perl, PHP, or even Java, you have to install classes or modules to connect to ODBC (unless you know the language well enough to code it yourself, but you'd rather download code that's been tried and tested).

Not all languages encourage ODBC, but a lot of them support it one way or another. For example, Java uses JDBC for databases. If you want to connect to an ODBC data source (database) then you'd have to go through JDBC to get to ODBC.

As far as being more powerful I can't honestly say so because I haven't worked with anything else under a Windows platform. A lot of my data sources are from Microsoft (SQL Server, Access database). I'm using Oracle right now for a project and I connect through JDBC. From what I've heard about ODBC is that it's slow. But mind you that I've heard this from Unix and Linux groups who just follow the crowd on hating anything that Microsoft does without even stating why it's slow. ODBC is a standard used professionally around the world for a lot of commercial applications. It's used where I currently work and we get huge amounts of hits daily around the world.

One thing that I have to say is that ODBC is not a Microsoft standard. They might have been the first ones that released it commercially, but certainly
did not create it. ODBC came from the SQL Access Group, based on the Call-Level Interface I believe.


Here are different ODBC drivers:
http://ourworld.compuserve.com/homepages/Ken_North/odbcvend.htm

I have to agree. My experience with both JDBC and ODBC, as strange as this sounds, as shown that JDBC is faster. Which makes you wonder since JDBC requests through ODBC to a datasource. Not that I understand it completely. But I have created a forum in ASP and in JSP and the JSP forum loads far faster than the ASP one..

Just thought I would throw in my two bits.