[RESOLVED] VB6: Connect Blank Database To VB6

[JWJWJW]

Hello,

I'm working on a program, that should be completely editable whil running. All of that (posititioning buttons, editting colors,etc) is not my question for now.

I want to connect a Database from access (2007) to my project in VB6. I tried working with queries, but somehow i can't get it to work properly.

example of my database
name: Sports
column A : ID-numbers (all unique)
column B : Sportgroup (ballsport, atletics, extreme-sports, etc)
column C : Specific Sport (for ballsport: baseball, am. football, etc.)
and some other information

What i would like to do with the program is this:
2 comboboxes
first combobox contains all from column B --> Select sportgroup
combobox 2 addapts to combobox 1 --> Select sport
Then, some information, Game Rules, Tips, etc show up


So far so good. If i want to make this a fixed program (user can't add or remove group/sport) i'd be ready by now.

What i want is that the user can add an item to the combobox, and keep it there the next time the program is started.

My questions:
Basic question = How can i connect a database to vb6?
I have no access to the northwnd.mdb file (my office blocks downloading), so that's why i could work with the tutorials i found.

How can i connect a column from an access-file to the combobox?
How can i make the 2nd combobox addapt to that one?
how can i make an additem button, writing it in the database?
and how can i make it working the second time i run the program?

thanks in advance.

[RhinoBull]

My questions:
Basic question = How can i connect a database to vb6?

It's the other way around... You connect your VB application (front end) to database (back end).
For samples and tutorials follow this link.

[si_the_geek]

Welcome to VBForums

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

[JWJWJW]

Hello,

Thanks for moving it to the right place. Also thanks for the reply with a link to the demo. I'm trying to understand the demo, to make the code compatible for my program.

I only have a few more questions about the demo:

Connecting to file:
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\DB1.mdb"
is app.path always the same directory (vb98 map)?
if connecting to an other file ("\Other.mdb"), would the rest of the code above be the same?

FillFields?
what does it do? (i think it moves the lower cells of the database up, to cover up the non-filled records?)

Ending:
If (rs.State And adStateOpen) = adStateOpen Then
rs. close
Uhm... question...all of that means???

explination above (rs.State.....) is the same for cn.State?


Thanks in advance

[si_the_geek]

is app.path always the same directory (vb98 map)?

App.Path is the folder that your executable file is in.

If you are running the code in VB itself (rather than as an executable file) it will be the VB98 folder.

You can specify the entire path if you prefer, and in many cases that is a good idea.

if connecting to an other file ("\Other.mdb"), would the rest of the code above be the same?

Yes.

FillFields?
what does it do? (i think it moves the lower cells of the database up, to cover up the non-filled records?)

No, what it does is copy what is in the "current" record (whichever the recordset is pointing to) into the textboxes etc.

It changes nothing in the database.

Ending:
If (rs.State And adStateOpen) = adStateOpen Then
rs. close
Uhm... question...all of that means???

The If line checks if the recordset is currently open or not.

If it is open, the second line closes it.


Closing is needed because otherwise you can cause damage to the database. Both lines are used because you cannot close something that is already closed - instead you would get an error.

explination above (rs.State.....) is the same for cn.State?

Yes.

[RhinoBull]

App.Path is the folder that your executable file is in.

Or project itself for that matter.

[JWJWJW]

Ok, great,

I kinda understand the code now, thanks very much for the support.
tiny question: if you make an .exe file of it, can you make it an .exe file only (so the access-file isn't public)?

thanks

[si_the_geek]

No you can't I'm afraid.

What you can do is give the database file a password (and/or other security features in Access) to help stop people getting in to it, and change the file extension so that they are less likely to notice what kind of file it is.

[JWJWJW]

Yes, a password would do, i guess. Thanks!
but making it a different extension, wouldn't my link from the exe file be corrupted? since it's searching for \"FileName.mdb"

Other Question:
I'm creating my own program with the information i got from the Demo. I've created a database with products (they all have a name, unique product-numbers, there devided in groups, dimensions, etc)

I wan't to make a searchbar to search for the product-number (PN). when the " check"-button is clicked, i want the product name to appear in a label
this is a part of my code:

Private Sub cmdCheck_click()
Dim PN as string
PN = txtPN.text
    If PN = "" Then
         txtPN.Setfocus    'nothing happens, only setfocus
         Exit Sub
    Else    'Do i need to open rs and/or cn?? i didn't close it at form_load..         
      rs.find ("Product Number = PN")     'Product Number = column name
         lblOutput.Caption = rs.Fields.Item("Product Name")   'this gives an error
         rs.close          'Wikipedia had a page about ADO
         cn.close         'With a code for a search event
      Set rs = Nothing 'here's the link
      Set cn = Nothing '(http://nl.wikipedia.org/wiki/ActiveX_Data_Objects)
    End if
End sub

If i change ' ("Product Number = PN") ' , wich sometimes gives an error at the moment, into what i actually want to find (lets say: PN = 23665) than it display's it's name in the label. But that would mean my searchbar (textbox) isn't needed??

how can i search a column (from my database) for a product-number, i entered in a textbox?
Also, Can i put a picture filename (or any link to it) in another column, so i can view the product in a picturebox? (and also connect this with the product-number that was entered?)

Thanks again

[si_the_geek]

Yes, a password would do, i guess. Thanks!
but making it a different extension, wouldn't my link from the exe file be corrupted? since it's searching for \"FileName.mdb"

Only if you don't change it in your code too.

Note that if you add a password (or other features), you will need to change the Connection String in your code to suit it - see the link at the bottom of my signature for examples.

rs.find ("Product Number = PN") 'Product Number = column name

Unless PN is the name of a field in the recordset, that isn't going to work.

See the article about variables in the "SQL" section of our Database Development FAQs/Tutorials (at the top of this forum)

an error

In that case you need "a solution".

Not knowing what the error is makes it is harder to narrow down the possible solutions.

[JWJWJW]

BTW the link is dutch, so i'll translate what's needed from the code:

Usage:
find a phone-number in tabel "phonebook", searching by a name (in this case: KenDZ). In my program, i want KenDZ to be a variable, depending on the text in a textbox. Code for phonenumber program:

dim dbcon, rs, phonenr
set dbcon = server.createobject("ADODB.Connection")
set rs = server.createobject("ADODB.recordset")

dbcon.open(mydatabase)
rs.open("Phonebook", dbcon)
rs.find("Naam = 'KenDZ'")        'use the method 'find' to find the right record in the tabel
phonenr = rs.fields.item("Phonenumber") 'Get the info out of the correct column (field) out of the record.

'Close
rs.close
dbcon.close
set res = nothing
set dbcon = nothing

Wasn't much, but helps you understand the names

thanks

[JWJWJW]

HMM, thats fast thanks

The error i got for:

...
Dim PN as string
PN = txtPN.text
...
rs.find ("Product Number = PN")

is:

Run-time error '3001':
The arguments are of the wrong type, are out of the permitted reach or are in conflict

My VB is dutch, so this error had to be translated, and might contain certain errors itself

my guess would be: Dim PN as String is wrong (should be variable??)

Thanks in advance

[si_the_geek]

Dim PN as String is fine (that is a variable), the problem is how you use PN on the rs.Find line.... the FAQ article explains how to correct it.

[JWJWJW]

I prefer working without SQL for now, since I find VB6 allready is a lot of information to handle (I'm programming for my co-op at this company, and i study mechanical engineering, wich contains no programming at all).
Also, i couldn't find the correct solution for my defunct program.

Could you give me a piece of code, where a textbox.text is the text to be searched in a column of a database? It then should return the other data from the other columns in labels.

Sorry for the inconvenience, and thanks for the effort.

[JWJWJW]

Sorry,
getting a little of thread.
I'll set this one as solved.

thanks all!