Databases... oh my!

[MidgetsBro]

I have finally came to face the fact that I HAVE to learn how to do database development. How should I start to learn databases? I tried to do Karl Moore's tutorial on it, but I don't want to use a flexgrid control or data control. How can I just connect to my database I made in Access, send the SQL query "Select <insert item number> From ItemNo"? My database name is Inventory.mdb, and my table name is Current.

Thanks for any help
-Joey

[veryjonny]

Add a reference to microsoft Activex Data objects...

dim conn as adodb.connection
dim rs as adodb.recordset

conn.open "Provider=Microsoft.jet.oledb.4.0;Data source =" & app.path & "\Inventory.mdb"

'it is needed that u have your DB in your project folder
 


rs.cursorlocation = aduseserver
rs.cursortype = adopenkeyset
rs.locktype = adlockoptimistic

rs.open "current",conn
rs.addnew
rs("field1") = "value1"
'so on

'update the table
rs.update

'close connections
rs.close
set rs = Nothing
conn.close
set conn = Nothing

Hope this helps

[MidgetsBro]

veryJonny, Thanks for helping me, but that code you posted doesn't work. I added a reference to Microsoft ActiveX Data Objects (Multi-Demensional) 1.0 Library, and then put your code in my command button, but it didn't work. It said "User-defined type not defined."

Is there a way to do this without a flexgrid, but with a data control? I think I will be able to use just a datacontrol, but I don't like the flexgrid.

[veryjonny]

U added a wrong reference - add this :

Microsoft ActiveX Data objects 2.5 Library



to use the code u do not need Flexgrid/datacontrol.

[Joacim Andersson]

You've set the reference to the wrong library.
You should use Microsoft ActiveX Data Objects 2.x Library.

Best regards

[MidgetsBro]

Thanks. That must have been the problem. I'll try it.

[MidgetsBro]

OK now I am having a different problem. I am using this code, just to test, but now it gives me an "Object variable or With block variable not set" error in the highlighted line:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
 
Private Sub Command1_Click()
[color=yellow]conn.Open "Provider=Microsoft.jet.oledb.4.0;Data source=C:\Windows\Desktop\inventory.mdb"[/color]
rs.CursorLocation = adUseServer
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
 
rs.Open "current", conn
MsgBox rs.RecordCount
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub

[Joacim Andersson]

You must create the objects before you can use them.

Private Sub Command1_Click()
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    'the rest of the code
End Sub

Best regards

[MidgetsBro]

Thanks Joacim. That worked. I think I'll start to figure it out from here.

[veryjonny]

Sorry MidgetsBro

My mistake.

U shd change this line right at the top.

dim conn as newadodb.connection
dim rs as new adodb.recordset

[MidgetsBro]

Arg. Sorry to bother you guys again, you have been a great help. This is exactly why I have been avoiding databases. Now I get an Invalid SQL statement in the 'rs.Open "Current", conn' line. It says it expects DELETE, INSERT, PROCEDURE, SELECT, or UPDATE.

Thanks for all the help you have given me so far. I hope I get this over with soon.

[veryjonny]

Shdnt happen in normal circum. , Are u trying to do something else?

[Joacim Andersson]

You should change the word "current" to a valid SQL statement.

rs.Open "SELECT * FROM [i]TableName[/i]", conn

Best regards

[MidgetsBro]

OK forget databases... I just fix one error, and I get another. I'm just not going to deal with databases anymore. I'll just write code to search a deliminated text file or somthing.

Thanks for you help though, it got me somewhere at least. If I ever try to attack them again, then I'll have a headstart.

[gab2001uk]

Don't give up yet!
Remember every error that crops up, and you solve by yourself, or others help you with, means you have climbed further up the learning curve.
It is unfortunate the posts so far have used the inherently more complex ADO code.
Although now going out of date, DAO code is much simpler for the beginner.

[MidgetsBro]

Can you help then? I just want to connect to my database, use an SQL statement to select the item number I type it, then it returns the price for that item number. I just want two textboxes, one you type into, and then click the ok button, and it opens the database, retrieves the price for that item #, puts it in the second textbox, then closes the database.

Can you show me how to do this with DAO? Is it really that much easier? I don't care if it is going out of date, I just want to know that I did SOMETHING with databases.

Oh and about 3 days ago, my signature said trying is the first step towards failure... it still holds true for me, so I think I'll put it back in there.

[gab2001uk]

First add reference to Microsoft DAO v 3.6 to you project

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
 
Set db = OpenDatabase(app.Path & "Inventory.MDB") 'change path here if not in project path
 
'now construct SQL
 
strSQL = "SELECT * FROM Current WHERE YourField = ItemNo" 'substitute real field name and value of itemNo
 
'open recordset
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
 
If rs.RecordCount > 0 Then
    Text1.Text = rs!YourField & "" 'substitute real field names
    Text2.Text = rs!YourField2 & ""
Else
    MsgBox "No Match"
End If
rs.Close
db.Close

[Wokawidget]

Try this, it uses the reference to ado (see above):

Oh and it has 2 text boxes called txtPrice and txtItemNumber. Also there is a command button called cmdFetch.

Private Sub cmdFetch_Click()
Dim adoConnection   As ADODB.Connection
Dim adoRecordset    As ADODB.Recordset
Dim strPath         As String
Dim strSQL          As String
    If Trim(txtItemNumber.Text) = "" Then
        Beep
        MsgBox "Please enter an item!"
        Exit Sub
    End
    If Not IsNumeric(txtItemNumber.Text) Then
        Beep
        MsgBox "Item number must be numeric!"
        Exit Sub
    End If
    strPath = "Path location of you database ie: C:\WinNT\Woof\"
    Set adoConnection = New ADODB.Connection
    adoConnection.CursorLocation = adUseServer
    adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data source =" & strPath & "\Inventory.mdb"
    strSQL = "SELECT Price "
    strSQL = strSQL & "FROM TableName "
    strSQL = strSQL & "WHERE Item_Number = " & txtItemNumber.Text & " "
    Set adoRecordset = adoConnection.Execute(strSQL)
    With adoRecordset
        If Not .EOF And Not .BOF Then
            txtPrice.Text = Format(.Fields("Price"), "Currency")
        Else
            MsgBox "Item not found!"
        End If
    End With
    adoRecordset.Close
    Set adoRecordset = Nothing
    adoConnection.Close
    Set adoConnection = Nothing
End Sub

Does this work?

[honeybee]

ADO or DAO does not really make much difference. Although using DAO with Access is fairly stronger than using ADO, there really is no harm in trying out ADO first.

MidgetsBro, you might be very anxious and be upset on getting errors every now and then, but don't worry, you have experienced members (and I am not referring to myself ) to help you out.

The errors you have encountered so far were very basic ones. First, you already know that before you can use objects from a class, you have to create an instance of the class, that's what you forgot to do with the Connection and the Recordset objects earlier. Next, with the rs.Open statement, you have to pass a valid SQL query, and "current" was not the valid SQL query. Imagine you are using SQL editor. Would you type "current" there to get your records? You would type "Select * from Current" and that's what you should use while opening a recordset.

And also a working knowledge of the SQL language is required for manipulating the databases.

.

[Muds]

Here's how I do it. And guys if this way is not efficient or if it is not a better way of working with DB Please let me know. B'cos no one taugth me how to work with db's . I learned these my self.

I dont use References and all that. ( I dont know there names)

Add a DataEnvironment to the Project
right Click the Dataenvi.. and add a connection
Change the propeties of the database. (Connect to your db)

Ok that's it Now start coding
Dim SQL
Dim Rs as adodb.Recordset

DataEnvironment1.Connection1.Open
SQL = "SELECT * ...."
Set Rs = DataEnvironment1.Connection1.Execute(SQL)
SQL = "INSERT "
DataEnvironment1.Connection1.Execute (SQL)
DataEnvironment1.Connection1.Close

If this doesn't work
tell me I must have missed something

[InvisibleDuncan]

If you want to open an entire table without building an SQL statement to do it, just specify that you're opening a table:

rs.Open "TableName", , , , adCmdTable

As a general rule, I'd avoid using DataEnvironments and DataControls. They suck.

[goudabuddha]

I hardly ever use databases, i just write stuff i need into a text files and i can specify the read/write functions as needed

[MidgetsBro]

Originally posted by honeybee

MidgetsBro, you might be very anxious and be upset on getting errors every now and then, but don't worry, you have experienced members (and I am not referring to myself ) to help you out.

I don't get anxious when I get a few errors now and then, but with this, I got one error after the other. I asked a question, Joacim or vj answered, then a new error popped up when I fixed the old one. I didn't get the code to work at all. I will try that DAO code.

Originally posted by goudabuddha

I hardly ever use databases, i just write stuff i need into a text files and i can specify the read/write functions as needed

That is EXACTLY what I do. I rather take the time to write functions to read and parse text files. It's much easier to me, but I am going to have to learn Databases sooner or later.

PS... What the hell is the difference between ADO and DAO? A transposing of two letters?

[MidgetsBro]

gab2001uk... your code worked for me! That is what I need. Just simple code that works, that I can build on. Thank you for helping me. I am going to try workawidget's code and see if I can get that to work, too.

Thank you all for helping me. I feel like I have started on a good learning adventure through databases...

[Edneeis]

Here is an ADO example of what you wanted. I like examples I think they are easier to learn from.

[MidgetsBro]

Thanks Edneeis, but I don't have the 2.5 library. Does that come with SP5? I only have SP4, and a 56k connection, so I can't download SP5. I'll look at the code though and study it. I got that DAO code that gab2001uk posted, so I think that should be enough for right now.

[Edneeis]

That's fine what version do you have. You can probably just change the reference to an eariler version, although I can't vouche for any of them before 2.5. Also you can download the latest MDAC without downloading the whole Service Pack. It would be my recommendation to just deal with the big download and get the service packs though, might save you a headache later if you run into one of the bugs they fixed.

I did test the app with 2.0 and it worked.

[gab2001uk]

Well at least between us we managed to keep you going, and not give up.
Happy learning.
Oh...The difference between DAO and ADO?
DAO is the native code for Access databases, but because Micro$oft thought they might miss out on global domination, they invented ADO, which can connect to a larger number of (Rival) databases, and are actively trying to push it as the way to go.
There are many things you can't do with ADO, and on one of their ADO sites, they tell you to use DAO code!

DAO is also at least 2-5 times faster than the ADO equivalent.

[MidgetsBro]

I have two different versions of ADO. I have 2.0 which is msado20.tlb and I have 2.1 which is msado15.dll. I tried with both of them, and I got the same error with your code.

[Beacon]

Who's code?

Just reading this would deter me from using databases i dont know how you made sense of all this.

Perhaps your problem is in your recordset line with the SQL statement. I.e your using adcmdTable instead of adcmdtext or something.

One thing DAO is good but MS aint gonna be supporting it in the future as far as i know. Frankly if you use ADODB correctly then it would be hard to detect the difference in speed.
I have several huge db's at work some in SQL some in access and i cant notice much difference between dao and ado i even tested a 1.5gb access db 1 prog used dao and 1 used ado and there was little to no difference.

I couldnt put up with using text files that must take so long and be so annoying. Plus they be huge and lots of coding.

later
b

[MidgetsBro]

Edneeis' code for ADO didn't work for me. It's just because I don't have the updated reference files that he used. I'm fine with DAO for right now. I don't need it for any big projects. I just wanted to know what it did finally. I know I might need it in the future, but for now, I know enough. I can probably use a database in my chat/filesharing program instead of loading my textfile into a listbox, and searching it for the username and password. I'll be fine for now. Thanks everyone who helped me.

[cargobay69]

To learn all you need to know about VB and databases, I highly recommend Beginning Visual Basic 6 Database Programming by John Connell.

[Edneeis]

What error did you get? Although it sounds like we are beating a dead horse here.

[MidgetsBro]

I get the same user-defined type error that I get if I don't add a reference to the dll with the DB functions in it. I think you are beating a dead horse... (that's a good analogy ) I've got the code to do what I need for now, cause it's just simply searching for an item number typed into the textbox, and finding it, and returning the price. Kind of like a cash register.