Query an access database via SQL in VB6 
Author Message
 Query an access database via SQL in VB6

Hi there,
I am developing an application that allows the user to input information onto a form which is in turn saved to an access database. The user can recall information that they input into the database via a second form which is written to several tet boxes using a data control. I would however like the user to be able to query the database and recall any record instantly and write the data to several text boxes rather than labouriously scrolling through all entries to find the required info.
I know that i need to use SQL to achieve this but do not know how to incorporate the SQL statement into the code.
At present ALL I have is the following statement attached to a command button but am unable to recall the information from the database. What else do I need in order to get this working? Can anyone please help?

Data1.RecordSource = "Select [*] FROM [Faults] WHERE [Fault Ref] = text1.text"
Refresh

Cheers
Chris



Fri, 21 Mar 2003 03:00:00 GMT  
 Query an access database via SQL in VB6

1.) Download the latest MDAC from http://www.microsoft.com/data (v 2.6)

2.) Create a SystemDSN using the 32-bit Data Sources applet in the
    Control Panel that points to your Access Database using the Access ODBC
    driver

3.) Make a reference to the ADO (ActiveX Data Objects) library in your VB Project
    (Project -> References)

4.) Create a ADODB.Connection object and an ADODB.Recordset object

5.) Write your code like this:

'--------------------------------------
Dim cn As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String

sSQL = "SELECT * FROM Faults WHERE [Fault Ref] = '" & Text1.Text & "';"

cn.Open "DSN=YourDSNName;"

Set rst = cn.Execute sSQL

'Use the recordset
'--------------------------------------

For more information on ADO, view:
http://msdn.microsoft.com/library

+PlatformSDK
+-Data Access Services
+--Microsoft Data Access Components
+---Microsoft ActiveX Data Objects
+----Microsoft ADO Programmers Reference

-Chad


  Hi there,
  I am developing an application that allows the user to input information onto a form which is in turn saved to an access database. The user can recall information that they input into the database via a second form which is written to several tet boxes using a data control. I would however like the user to be able to query the database and recall any record instantly and write the data to several text boxes rather than labouriously scrolling through all entries to find the required info.
  I know that i need to use SQL to achieve this but do not know how to incorporate the SQL statement into the code.
  At present ALL I have is the following statement attached to a command button but am unable to recall the information from the database. What else do I need in order to get this working? Can anyone please help?

  Data1.RecordSource = "Select [*] FROM [Faults] WHERE [Fault Ref] = text1.text"
  Refresh

  Cheers
  Chris



Fri, 21 Mar 2003 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Export Access Queries to a Word Doc via a VB6 Program

2. Exporting Access Query SQL via WSH

3. Setting SQL Server query processing options via Access/VBA

4. SQL statement and Access via VB6

5. VBScript- SQL Query of MS SQL 7 Database

6. Help vb6 sql query to access

7. VB6, access 2000 and SQL query

8. ADO Query into Access 97 Database using VB6

9. ADO Query into Access 97 Database using VB6

10. SQL Query with multiple Access databases

11. SQL Query in Access Database

 

 
Powered by phpBB® Forum Software