Writing to an Access Database

[n1cole]

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

[samaru]

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. :)

[n1cole]

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.

[Tekmaven]

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!

[samaru]

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.

[samaru]

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.

[Dani]

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?

[aeinstein]

What would be an alternate to ODBC?

[samaru]

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

[Paladine]

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.

[samaru]

Glad you agree. I don't think though your test on which one (JDBC or ODBC) was faster was fair however. Where you testing the speed of the database APIs or the forums? Classic ASPs are interpreted and JSPs are turned to servlets and are compiled, so they (JSPs) would most of the time run faster than interpreted ASP pages.

[aeinstein]

First, thank you for your post inscissor, and please accept my apologies for such a belated reply.

Second, let me state my knowledge on this subject: zilch, nada, nothing and pretty much zero - but I am quite interested.

Thrid, why would it matter in justifying which technology used to accomplish the task? In this day and age it seems that speed reigns supreme above all else, so why use ASP over JSP?

[samaru]

Well for a lot of reasons. This question reminds me of myself and my old high school mentality. I would ask myself, "If C++ has one of the fastest execution times, why would any company use anything else? Anything else (like VB, COBOL, Delphi) would be slower." This was bad thinking. But it was ok though. I didn't know much about real world programming or the business world.

If I'm your boss and I tell you to make an application that generates invoices, and be meticulous on the GUI portion, what would you use? Oh yeah, and I told you that I need it tomorrow morning. Would you use Assembly? Visual C++? Visual Basic? I think we know the answer to that. Rapid software development matters. Execution time is not always an issue. It does matter, and it should, but creating the whole application in Visual C++ would not be worth it for just gaining some performance time. Well, not for this specific application. You could if you wanted create libraries in C++ that would handle any load processing from Visual Basic. But you get the idea.

Also, what if you were under a Unix/Linux OS? Could you use VB? Nope. You'd be limited to the tools available under Unix/Linux (which are a lot by the way).

The same issue applies to web languages. Rapid software development could be an issue. In this case we could use ColdFusion. But ColdFusion costs money... so then the current budget would be an issue. We would need to go with something that was free or low cost. We could go with something like PHP, Perl or J2EE. But what if my staff is used to using Visual Basic and Office products? Do you think learning ASP/ASP.NET (which emphasizes strongly on VBScript) would be easier compared to ColdFusion, PHP, Perl, or even Java? This is a tough question. Just because it runs the fastest doesn't always mean it's the best.

Still there are debates on which runs the fastest (mod_perl, PHP with an accelator like Zend's, compiled Java/ASP.NET, or ColdFusion (which now compiles to Java byte code). Wherever you go, they claim that they're the fastest. If you go to a PHP web site, they claim it's the fastest, if you go to a Perl web site, they claim it's the fastest, and so on.

So before you go and select a server side language or application server, you should consider the following:

1. Company Budget (always be concerned about how much you'll save and how much you'll make). Be aware of open source solutions and how much money it will save your company.
2. What technologies you have already. What OS? What database server? How many computers and what types?
3. How easy will it be to pickup the language? If your staff already knows Visual Basic, why not ASP/ASP.NET? Why Java? If Java, what are the benefits?
4. When does the web site go live? How long will it take to develop? Does everything have to be built from scratch?
5. Does the technology you're choosing come with all the tools you need to create your application? For example, if you want to upload in ASP, you'd have to buy or find a free a component to add this functionality. Coldfusion comes with this. Java and ASP.NET already come bundled with a lot of functionality.
6. How easy will it be to extend/maintain/debug the application? To update? Will the syntax and facilities within the technology make it easier? How well will it integrate with the current technologies you have?
7. How does the application server (what runs ColdFusion, ASP, PHP, etc.) handle under load? Is it scalable?
8. How easy is it to get support? Is there a strong community?
9. If you go with technology you pay for, you have someone to point the finger at if something goes wrong. With open source software you have little liability. You don't have one single company you can sue that owns it. If you pay for a service, you're paying for a reason; to get a guarantee that it will work (even though this is not always the case).
10. Does the technology have a future? You don't want to spend money buying a technology and find out it will be discontinued soon.
11. How does the technology handle security? Internationalization? How does it handle external services (HTTPS, web services, LDAP) and other protocols?

Those are a few issues to consider. As you can see, you always start any answer to a question with "it depends." Even if someone asks you "what's 1 + 1?" You say "it depends, is it in binary?"

Hope this helped!

[eschodde]

I likewise am having some difficulty writing to a database. However, I am using a Microsoft tutorial that came from their website. Here is the link.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnasp/html/asptutorial.asp

I have followed its steps, and when I try to view it and test it on my localhost (which is running PWS and IIS 5.0), I get this error on my page "Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/tutorial/guestbook.asp, line 21

strProvider = "Driver={Microsoft Access Driver (*.mdb)};

My root folder is c:/Inetpub/Wwroot/tutorial as they have suggested. However I noticed that my DSN was located in my

C:\Program Files\Common Files\ODBC\Data Sources

could that be the problem?

thanks,

[samaru]

Unterminated string constant

/tutorial/guestbook.asp, line 21

strProvider = "Driver={Microsoft Access Driver (*.mdb)};

Well, first of all, that error has nothing to do with the location of the database. You're getting that error because you're not closing the string with a quote. Just put a quote a the end that string, like this:

strProvider = "Driver={Microsoft Access Driver (*.mdb)};"

Then you will probably have another error now. :cool: That string defines the connection and you have not provided the location of the database, just what driver to use, in this case, a Microsoft Access Driver. Look carefully at the sixth post of this thread where I show an example of how to access a database. Look at the second example.

If you have any other questions, ask away! :cool:

[Paladine]

Glad you agree. I don't think though your test on which one (JDBC or ODBC) was faster was fair however. Where you testing the speed of the database APIs or the forums? Classic ASPs are interpreted and JSPs are turned to servlets and are compiled, so they (JSPs) would most of the time run faster than interpreted ASP pages.

Agreed! My Bad! Thanks for the correction!

[samaru]

Glad you agree. Whoa, almost a year already since I said that.

[Rudner D. Diaz]

hello everyone,
i just want to ask any idea about my problem..i have an access file that i want to it be used as my datafile and i'm using vb.net to access this file..as of now i works fine if i path the file to the machine where my vb.net installed..and i want to make a program that will be used by many users thru vb.net and access as my back end... as of nowI have a sever(windows server 2003) which have a domain..all the user are logging in with thier password and username to have shared some files..my question is....

1. how to set up my access file if i put it in my server?

i want that every time i make a code in vb.net, i path will go directly to my server where my access file reside.

hope u get my problem..

thank you

[paramesh_dreams]

hai,
i want to kno about the vb 6.0. so that i started to work with the project, not a high level, i m in beginner level, i connected with access and ADD is functioning i want to know how to delete the record that is full record and checking the condition. its the ADD coding
ADD coding to add the datas in access
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Gopi\employee\db1.mdb"
cn.ConnectionString = Connstr
cn.Open()
Dim qry As String
qry = "insert into emp1 (emp_id,emp_name,emp_phne,emp_add,emp_mail_id) values(" & TextBox1.Text & ",'" & TextBox2.Text & "'," & TextBox3.Text & ",'" & TextBox4.Text & "','" & TextBox5.Text & "')"
Dim Olecmd As OleDbCommand
Dim Olduprdr As OleDbDataReader
Olecmd = New OleDbCommand(qry, cn)
Olduprdr = Olecmd.ExecuteReader
cn.Close()
End Sub

[zanzo]

helllo,
I do have a problem with access...

this is my code:
<%

var connection= new ActiveXObject("ADODB.Connection");
connection.Provider="Microsoft.Jet.OLEDB.4.0";
connection.ConnectionString = "Data Source=" +Server.MapPath("/database/Training11.mdb");
connection.Open;

var SQL = "INSERT INTO User (FirstName, MiddleName, LastName) VALUES ('lolo', 'keymo', 'rub')";

connection.Execute(SQL);
connection.Close();

%>

And i do get this error message:

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/testy.asp, line 16

so how could i solve this problem??
do i have some problem in the insert query??
help!!