using sql in the code window

[urbanangel]

Hi ,

I am developing an application for a friend. I can only use ACCESS 2000. Please note that the entire application will have to be built in access. Now I need to embed sql in the vb code window how I am unable to do so.
Please help

 

[FoFa]

Here is one an example of how.

Sub ADOExecuteBulkOpQuery()

   Dim cnn As New ADODB.Connection
   Dim iAffected As Integer

   ' OR if MS Access
     Set cnn = CurrentProject.Connection


   ' Execute the query
   cnn.Execute "UPDATE Customers SET Country = 'United States' " & _
      "WHERE Country = 'USA'", iAffected, adExecuteNoRecords

   Debug.Print "Records Affected = " & iAffected

   ' Close the connection
   cnn.Close

End Sub

 

[Moniker]

Fofa's response will work, but can be simplified a little by using the built-in connection, like this:

Sub ADOExecuteBulkOpQuery()

   Dim iAffected As Integer

   ' Execute the query
   CurrentDb.Execute "UPDATE Customers SET Country = 'United States' " & _
      "WHERE Country = 'USA'", iAffected, adExecuteNoRecords

   Debug.Print "Records Affected = " & iAffected

End Sub

Also note that the iAffected and the Debug.Print are just to show you that it's working. They are not necessary. You can just use this anywhere:

CurrentDb.Execute "SQL_Statement_Here"

 

[urbanangel]

Thanks, actually i will only be using access. I will have to pass a variable & based on that i will be executing a sql ... something like this ..


Private Sub TXTnameMASTER_Click()
Dim nn As String
Dim iAffected As Integer

nn = TXTnameMASTER
MsgBox (" selected " & nn)
CurrentDb.Execute "Select * from Table1 ", Affected, adExecuteNoRecords
CurrentDb.Execute "UPDATE master SET name = 'Menoka' " & _
"WHERE name = 'meena'", iAffected, adExecuteNoRecords

End Sub