Connecting to Access Database and running Queries

[Dazza203]

Hi,

I want to connect to a Access 2003 database using VB 6 and then by clicking on a command button I want to run an sql query from a specified attribute in a specified table from the Access 2003 database. The problem is there are 4 different text boxes on the form and I want a query to be run on a different attribute in the same table depending which text box has data entered into it. I want this all to run from the same command button and I want any search results from the queries to be displayed in a list box. Any help for the code for this would be appreciated. I also don't know any of the code to connect to the database. I would also appreciate it if you could tell me under what sub routines each piece of code would go under. From reading tutorials I have come up with this code and I'm sure I'm close with it:


Dim ADOCn As ADODB.Connection
Dim ConnString As String
Dim adoRS As ADODB.Recordset
Dim sSQL As String

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\YourDB.mdb;" & _
"Persist Security Info=False"

Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
Set adoRS = New ADODB.Recordset
sSQL = "SELECT fieldname FROM tablename WHERE fieldname = '" & Text1.Text & "'"
adoRS.Open sSQL, ADOCn
Text2.Text = adoRS.Fields.Item("fieldname").Value
ADOCn.Close
Set ADOCn = Nothing
Set adoRS = Nothing
End Sub

Any help with fixing this code would be very helpful because I've been working on it for hours now and I'm starting to tear my hair out.

Thanks,
Darren

 

[jbpez]

Try something like this:

Dim ADOCn As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim sSQL As String
Dim sWhere as string
Dim varValue as variant

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\YourDB.mdb;"

Set ADOCn = New ADODB.Connection
ADOCn.Open ConnString

Set adoRS = New ADODB.Recordset

sSQL = "SELECT fieldname FROM tablename"
sWhere = ""
If Text1.Text <> "" THen sWhere = "fieldname1='" & Text1.Text & "'"
If Text2.Text <> "" Then
   If sWhere = "" Then
	sWhere = "fieldname2='" & Text2.Text & "'"
   Else
	sWhere = sWhere & " AND fieldname2='" & Text2.Text & "'"
   End if
End If
If Text3.Text <> "" Then
   If sWhere = "" Then
	sWhere = "fieldname3='" & Text3.Text & "'"
   Else
	sWhere = sWhere & " AND fieldname3='" & Text3.Text & "'"
   End if
End If
If Text4.Text <> "" Then
   If sWhere = "" Then
	sWhere = "fieldname4='" & Text4.Text & "'"
   Else
	sWhere = sWhere & " AND fieldname4='" & Text4.Text & "'"
   End if
End If

If sWhere <> "" Then sSQL = sSQL & " WHERE " & sWhere

adoRS.Open sSQL, ADOCn, adOpenForwardOnly, adLockReadOnly

Do Until adoRS.EOF
  varValue = adoRS.Fields("fieldname").Value
  ListBoxName.AddItem varValue 'this will add data to listbox
adoRS.MoveNext
Loop

adoRS.Close
Set adoRS = Nothing
ADOCn.Close
Set ADOCn = Nothing

look here for more info about connection strings

John Borges

 

[Dazza203]

That works thanks a lot. My only other question is what will I need to add if I want the query to look in 2 attributes for one of the text boxes?

Thanks,
Darren

 

[Dazza203]

Also I'm a bit confused as to which sub routine each bit would go under, could you show me which ones?

Thanks,
Darren

 

[jbpez]

All of the code would go under the following,

Private Sub NameOfYourButtonHere_Click()
'code here
End Sub

My only other question is what will I need to add if I want the query to look in 2 attributes for one of the text boxes?

Please explain, I'm a bit confused.

John Borges

 

[BiggerBrother]

use an OR statement, eg:

Select * where (myField1 = mytext1 or myfield2 = mytext1) AND field3 = mytext2

BB

 

[Dazza203]

Ok I have put that code into my program but I am getting an error saying 'Compile Error: User-defined type not defined' It then refers to the first 2 lines of code which isn't a good start. Here is the code I have

Private Sub cmdSearch_Click()
Dim ADOCn As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim sSQL As String
Dim sWhere As String
Dim varValue As Variant

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=Library.mdb;"

Set ADOCn = New ADODB.Connection
ADOCn.Open ConnString

Set adoRS = New ADODB.Recordset

sSQL = "SELECT Book Author FROM Books"
sWhere = ""
If txtAuthor.Text <> "" Then sWhere = "Book Author='" & txtAuthor.Text & "'"
If txtTitle.Text <> "" Then
If sWhere = "" Then
sWhere = "Book Title='" & txtTitle.Text & "'"
Else
sWhere = sWhere & " AND Book Title='" & txtTitle.Text & "'"
End If
End If
If txtKeywords.Text <> "" Then
If sWhere = "" Then
sWhere = "Book Keywords='" & txtKeywords.Text & "'"
Else
sWhere = sWhere & " AND Book Keywords='" & txtKeywords.Text & "'"
End If
End If
If txtPublicationDate.Text <> "" Then
If sWhere = "" Then
sWhere = "Book Publication Date='" & txtPublicationDate.Text & "'"
Else
sWhere = sWhere & " AND Book Publication Date='" & txtPublicationDate.Text & "'"
End If
End If

If sWhere <> "" Then sSQL = sSQL & " WHERE " & sWhere

adoRS.Open sSQL, ADOCn, adOpenForwardOnly, adLockReadOnly

Do Until adoRS.EOF
varValue = adoRS.Fields("Book Author").Value
lstCatalogueSearchResults.AddItem varValue
adoRS.MoveNext
Loop

adoRS.Close
Set adoRS = Nothing
ADOCn.Close
Set ADOCn = Nothing
End Sub

Thanks,
Darren

 

[johnwm]

Go to Project|References and tick the box next to Microsoft ActiveX Data Objects 2.x Library

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[Dazza203]

Ok that seems to work but I now get the same error but with the first ConnString being highlighted this time saying variable not defined. Do I need to put some of the code in the Option Explicit at the top of the page?

Thanks,
Darren

 

[jbpez]

You need to add

Dim ConnString as string

with the other variables.

On your connection string "Data Source=Library.mdb" should be a path like "C:\something\Library.mdb"

Also, I noticed some of your field names have spaces.
So, make sure you place them in square brackets

[Book Author]
[Book Title]
[Book Keywords]
[Book Publication Date]

not here - varValue = adoRS.Fields("Book Author").Value

John Borges

 

[Dazza203]

Ok, that all works now thanks a lot for all your help. There's now 2 other things I want it do do, In the loop I want it to display 2 different attributes in stead of just one and in the list bo I want it to display in this format

Attribute 1 Contents - Attribute 2 Contents

So I need to attributes with spaces and a dash in between but I don't know the syntax for this. Also I want to display a message in the list box if no records are found. What code do I need to add for this?

Thanks,
Darren

 

[unborn]

tag this may prove useful!

Running in circles is what I do best!