how to make insert,update,delete in ms access and vba using function (1 Viewer)

[ost3z]

**hi every body i want an example to show how to make insert,update,delete in ms access and vba using function where i can call them in any place thankx
i saerch more but i cant find my answer please help**

 

[pr2-eugin]

Hello ost3z, Welcome to AWF..

First you need a proper structure in order to do this.. How robust do you want this function to be? Is it always going to perform DML actions on one particular table?

See how UPDATE, INSERT, DELETE works.. Performing Insert would be more complex than the others..

 

[ost3z]

thank you very much pr2-eugin for your replaying i am c# programming and i know itis logic but itis the frist time i deal with vba code
so i need only example how to make this function and call it in ms access and vba

 

[AndrewWhitelaw]

My best advice is to create a query then view it using SQL View.

You can run the sql command as a string from within vba using DoCmd.RunSQL

DoCmd.RunSQL ("DELETE tempPerson.* FROM tempPerson")

or you can build and SQL string and execute it, this allows some very complex queries to be built and execurted at runtime.

The example below builds a query based upon the contents of 1 or more combos (if the combo contains data it is added to the query) and then runs it inserting the code into a table.

   strSQL = "INSERT INTO tempPerson ( PostCode, HouseNumber, Name )" & vbCrLf _
            & "select distinct Combined_Data.Postcode, Combined_Data.HouseNumber, Combined_Data.Name " & vbCrLf _
            & " FROM Combined_Data " & vbCrLf _
            & " WHERE "
            
    If Me.AddressCombo.Value <> "" Then
        strFilters = strFilters & " Combined_Data.HouseNumber=""" & Me.AddressCombo.Value & """"
    End If

    If Me.NameCombo.Value <> "" Then
        If Len(strFilters) > 0 Then strFilters = strFilters & " AND "
        strFilters = strFilters & " Combined_Data.Name = """ & Me.NameCombo.Value & """"
    End If

    If Me.PostCodeCombo.Value <> "" Then
        If Len(strFilters) > 0 Then strFilters = strFilters & " AND "
        strFilters = strFilters & " Combined_Data.PostCode=""" & Me.PostCodeCombo.Value & """"
    End If
           
    strSQL = strSQL & strFilters
    
    DoCmd.RunSQL (strSQL)

note my coding is not the best also the 'vbCrLf' is there to make reading it when debugging easier, it serves no function otherwise.

 

[mdlueck]

I find ADO objects best to work with, when possible. DAO objects when ADO objects will not work in the particular situation. I have worked up some samples here:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

Example of SQL SELECT using ADODB.Recordset object to Access FE temp table to scan the FE temp table and perform operations
http://www.access-programmers.co.uk/forums/showthread.php?p=1214730 #post1214730

 

[ost3z]

many thankx for your replies i am going to follow your advice in my querries
but i have anothe question

i have made aproject with customize toolbar which appears only after login i made it in access 2003 but the problem is that when i run the project in access 2007 or 2010 the customize toolbar appears even he didnt login what is the solution for that problem ??