MS Access retrieving records via SQL 1

[Versys]

Hi, everyone

I am trying to get an specific record by its key, if it exists, update it, otherwise add the entry to my data base (MS Access), and I would like to do this using SQL instructions like "select * from mytabe where mytable_key = txtSearchKey".

Thanks again for your support!!!!

Vinnie....

 

[fredericofonseca]

search these forums for "ADO" and "SELECT" and you will find may examples.

Once you have tried them came back with some code if you still need help.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[johnwm]

Sounds pretty straightforward. To get started read VBHelp, looking for ADO examples for VB. If you have some specific problem post the relevant code section.

Read faq222-2244 to find how to ask questions that get better answers!

________________________________________________________________
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?'

 

[Swi]

You can try something like this but make sure to read up on the subject to get a full understanding (See bottom for link):

Private Sub Command1_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

With conn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\YOURDATABASENAME.mdb"
.Open
End With

With rs
.Open "SELECT * FROM TABLENAME WHERE FIELDNAME = '" & Text1.Text & "'", conn
If .BOF And .EOF Then
conn.Execute "INSERT INTO TABLENAME (FIELDNAME) VALUES ('" & Text1.Text & "')"
MsgBox Text1.Text & " added to table!", vbInformation
Else
MsgBox Text1.Text & " already exists in table!", vbCritical
End If
End With

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub

http://www.w3schools.com/sql/

Swi

 

[Versys]

SWI

Thank you very much for you helpful answer. I forgot to mention that I have a good knowlegde of SQL, what I am trying to do is combined the Visual Basic that I am learning and get my information directly via SQL statements, however, It is very nice that you provide a direct answer and if you would answer me the following I will appreciate it: the use of SQL statements are always handle with ADO and Do I have to declare RS (understanting this as RECORD SET) ?

Thanks so much for you invaluable cooperation.

Have a nice day

Vinnie...

If you do not provide a solution you are part of the problem....

 

[Swi]

You could have also used DAO (which is actually a bit faster with JET engine databases) but ADO is newer and thus I used that method. RS is the recordset and in my particular example you would have to declare it so you can retrieve any existing entries in the database. The .BOF and .EOF If statement is one of the better ways to check for an empty recordset. Also, be sure to close all connections and recordsets and also set them to Nothing when using ADO or they will be held in memory.

Swi

 

[fredericofonseca]

Vinnie,

If you look at the ADO help you will see that the object model is made of several MAIN components.

You don't need to use them all, but what you use will depend on what you are getting back from the SQL statement issued.

e.g. if you are doing an insert you can use either the connection or the command object.

If you are doing a select you can use the command and the recordset object, or just the recordset object.



And others like this.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

Ah. Forgot one minor point.

you DO need to declare whichever objects you are going to use. I don't think this was the issue though.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com