Visual Basic and Access

I'm working on a school project in VB, and quite frankly, the teacher sucks...not teaching us what we need. My project involves using an Access Database and executing SQL statements on it and showing/inputing data etc. My problem isn't SQL, it's how to connect to the damn database....I'v googled it to death, and I'v not found any useful source or walk through, aswell as the books I have are useless. Soo....I turn to AO; any one here willing to show me how to create a connection to a database and how to open/close it and hopefully how to execute SQL on it if they have time.

Bleh....I need to go tell me teacher off for being useless!

- Noia

Using ADO to work with Access databases at msdn - http://msdn.microsoft.com/library/de...sdatabases.asp

hmmm...it worked...and then it was as if it was /.'d DNS failure. Still, didn't contain alof of Syntax as far as I could see, but I didn't get to read it all either.

They tell you further into that section how to make a connection to a jet database via ado, and then even further down how to work with it. You need to do the research to understand how it all works.

Hmm, yea...Just these books and tuts I'v read are all messed up....I'm really confused now :s

Are we talking VB6 or VB.Net?

Try this site it helped me.
http://www.freevbcode.com/listcode.asp?Category=4

VB6, sorry, should have mentioned.

This will get you a connection to your database.

dim cnn as adodb.connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Jet OLEDB:System Database") = c:\mymdw.mdw
.Properties("Jet OLEDB:Database Locking Mode") = adLockOptimistic 'row-level locking
.Properties("User ID") = "User"
.Properties("Password") = "Password"
.Open c:\mydb.mdb
.Properties("Jet OLEDB:Lock Retry") = 5
End With

'With the connection you can then create recordsets.
Dim rst as recordset
Set rst = New Recordset

rst.Open "Select * from MyTable", cnn, adOpenForwardOnly, adLockReadOnly
' Then loop through the recordset

I like VB, and I'm not to good with Databases, but good enough.
www.vbexplorer.com (forums and tuts)
www.planetsourcecode.com (ultimate for code examples)

Why don't you just use the built-in data control? I'm a bit rusty, but doesn't that allow you to connect to an access database and use SQL?

maybe a working project will help you out At least i think this is the working project. the install is good but thats 14MB. yup thats vb

Ok, I just got a lot better at databases after using them :) If you would like some RTH(real time help) IM me,
AIM: Wix 555 Lesser Evil
MSN(not paying so not sure if it still works): [email protected]

I hope I can help, I also found a neat article on protecting your databases.
http://www.advisor.com/doc/11530
This

Quote:

---

http://support. microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp

---

will not work because there is a space between "support. microsoft", the link should be http://support.microsoft.com/default...ent/secfaq.asp

-Sam-

I having the same problem too.

you can connect to a database using ADO here is a sample code if you are connection to the SQL server 7 database

dim con as adodb.connection
dim rs,rs_c,rs_f as adodb.recordset

Set con = New ADODB.connection
con.Open ("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=CivilSupplies") //civilSupplies is the name of the database
Set rs = New ADODB.Recordset /*ration_card_mast,citizen_mast,family_detail mast are the three tables in the database civilsupplies*/
rs.Open ("Ration_card_mast"), con, 1, 2
Set rs_C_Mast = New ADODB.Recordset
rs_C_Mast.Open ("Citizen_Master"), con, 1, 2
Set rs_F_Detail = New ADODB.Recordset
rs_F_Detail.Open ("Family_Details"), con, 1, 2

this is a sample code from one of my projects if you want more comprehencive sample or any other help regarding vb or any other topic you can e-mail me at [email protected]

i am attaching u a project i made when i was learning vb6 it a supermarket automation system it is a complete project u can use it as a reference

Most of the code posted looks good. But everyone who posted code forgot 1 important detail. In order for: "dim db as new ADODB.Connection" to istantiate the ADODB connection object a reference must be included in the project.

Project >> References >> Microsoft ActiveX Data Objects 2.5 library


'Begin Code

dim cn as new ADODB.Connection
cn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"dbq=C:\vb\inventory\inventory.mdb"

'End code


Also if you would like to test the connection state of the object (always a good idea you can toss in some generic conditionals such as:

if(cn.state=1) then isCon=true

Hope this helps.
sbg

Hello, i just turned in my VB + Access project yesterday. To connect to a DB there are two modes ADO and DAO. I used DAO, which is done by just adding the Data object to your project, this object has a property called databasename, choose the path in which you DB is stored. Next there is a property called Recordsource, here you choose the table on your DB to which you want to connect.
To execute SQL: (your data object is data1) data1.recordsource="select * from......
or data1.execute="delete * from.....

To connect using ADO, I have done it in Asp, if you want I will post details about it.
I am attaching a small example, just choose the BIBLIO database that comes as a sample in your VB folder and it should work.
If you want my full project or any further help, just say so.

J

Visual Basic 6.0 wont connect to access 2000 unless some sort of patch or upgrade is applied, check MS knowledge base for details

How do I find the data in DB, lets say before I want to add new the data into DB, I need to check the records(Primary key) in DB wheter there is similar value. And how to put it in array?

if you are useng vb as fornt end and acess 97 as back end u should try to handle constraints such as prinery key, enrorse data intigrity at the front end because giving constraints at the back end cuts the flexibility of using vb . u should try to enforce data entigrity at the vb ebd as much as possible

u can open a recordset (rs.Open ("Ration_card_mast"), con, 1, 2)
all the data in the table is retreaved in the recordset i.e. "rs" then u can one by one trasnsfer it into a array

Sice everyone has attached examples I will attach mine. This is just a sample app that uses VB as a front end to an Access 2000 DB for a little pretend store type thing with little shopping cart, however I dont like the ADO control **** so mine uses ODBC connector and passes actual SQL queries to the DB (So you can use any ODBC RDBM you want if you have the datasource). User must log in, Accounts are stored in the DB along with log entries like succesful logins or attempts etc. Keeps track of users totals and stuff,also has some OLE stuff. Hope this helps somebody, keep in mind its just a demo so the code doesnt do a whole lot of data validating against SQL injection or anything.

-Maestr0