Which command do you guys use and advise to use

[Eisasuarez]

Hi All,

I know you use recordsets to retrieve data using the SELECT statement.

I know you can use the connection or command promt to ammend the database i.e DELETE, INSERT etc...

I have seen people use the command or connection method but which is the best and preffered way so i can get in to a habbit of doing it the right way.

Sub Connect_Database()


Dim cmd As ADODB.Command
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strDBPath As String


Set ws = Sheets("Sheet1")


strDBPath = "C:\Users\Matt\Desktop\Excel VBA Examples\VBA Examples\VBA 2010\SampleDatabase3.accdb"
    
    connection_string = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                 "Data Source=" & strDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;"
      
[COLOR=#ff0000]    ' The below SQL string would and should only be used in a Recordset as we are retrieving Data[/COLOR]
[COLOR=#ff0000]    'strSQL = "SELECT * FROM users WHERE tblactor LIKE 'A*'"[/COLOR]
    
[COLOR=#ff0000]    ' The method below could use the command or connection method to execute as we are ammending the database[/COLOR]
[COLOR=#ff0000]    strSQL = "UPDATE [users] SET [UserName]='Matthew Martell' WHERE [UserID]='MATTHEW'"[/COLOR]
   
    '//Connetion method to Open and execute SQL string
    
    Set cn = New ADODB.Connection
    With cn
        .ConnectionString = connection_string
        .Open
[COLOR=#0000ff]        .Execute strSQL[/COLOR]
    End With
    
    '// Command method to execute SQL string
       
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = strSQL
[COLOR=#0000ff]        .Execute[/COLOR]
    End With
        
          
    
End Sub

 

[xenou]

Hi, there is no difference if you are running select queries to retrieve recordsets. Use whichever you like (actually you can use the connection object, recordset object, or command object to return recordsets!). Use the command object if you want to use the Execute method to run Update, Insert, or Delete queries. I believe I typically use the command object if I am working with with parameters, also.

For what it's worth, my habit is to open a connection with a connection object first. Then use a recordset object if I want to run a select query to return a recordset. I use the command object when want to use it's Execute method or to add parameters to a query.

This is rather messy in a way. You can pretty much use all the top level ADO objects to do many things, and they can often be used to do the same things. I suppose that's a good thing - it's very flexible. If you wanted to only use the connection object whenever possible that might appeal to some people -- sort of a one-size fits all approach.

 

[Eisasuarez]

Your worth is alot Thank You

I will follow you and learn from the best out there

So
Connection to OPEN
Recordset to RETRIEVE
Command to Ammend DATABASE

Can you please give an example to these parameters and what they are and how to use in code as i am still learning the PARAMETERS in access.

P.s how can i amend the SQL string to use a stored procedure?

So say i had a
stored procedure called (SELECT_USERS) SELECT STATEMENT
stored procedure called (UPDATE_USERS) UPDATE STATEMENT

how can i add this to the SQL string rather than writing out the statement and i guess i would use command or recordset depending on which one i use?

 

[xenou]

A stored procedure is called by name, not with a sql string. You have to write the stored procedure in the database first. Only then you can call it with ADO. Access doesn't have stored procedures so I don't have any examples. Parameters are common. See example here:
Command Object Parameters

 

[Eisasuarez]

Hi Xenou

isnt stored procedure the same as a saved query?

if not whats the difference

stored procedure like a saved macro? But i thought saved query did the same thing ie run a set of instructions?

 

[xenou]

Hi,
Stored procedures is not part of the vocabulary of Access, normally. You can save a query. You can also save macros and/or vba procedures. These can all be used as stored procedures -- you just set them up to run whatever instructions you want them to run.

 

[Eisasuarez]

Thank You

I am still not sure what parameters are

I know in Access you can have a parameter to allow an input in a where clause

So my SQL would look like this

SELECT NewUsers.User_ID
FROM NewUsers
WHERE NewUsers.User_ID Like [Enter Name to start with] & "*"
ORDER BY NewUsers.User_ID;

I am not sure how this whole command thing works with Parameters and this whole append/create parameter

Any chance you could step through that with me and is the parameter the same as the create parameter in a where clause?

Thanks

 

[xenou]

This is an example:

[COLOR="Navy"]Sub[/COLOR] ImportRecordsetACE()
[COLOR="Navy"]Dim[/COLOR] cn [COLOR="Navy"]As[/COLOR] ADODB.Connection
[COLOR="Navy"]Dim[/COLOR] cmd [COLOR="Navy"]As[/COLOR] ADODB.Command
[COLOR="Navy"]Dim[/COLOR] param [COLOR="Navy"]As[/COLOR] ADODB.Parameter
[COLOR="Navy"]Dim[/COLOR] rs [COLOR="Navy"]As[/COLOR] ADODB.Recordset

[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]

    
    [COLOR="Navy"]Set[/COLOR] cn = CreateObject("ADODB.Connection")
    [COLOR="Navy"]Set[/COLOR] rs = CreateObject("ADODB.Recordset")
    [COLOR="Navy"]Set[/COLOR] cmd = CreateObject("ADODB.Command")
    
    
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"    [COLOR="SeaGreen"]'Connection to an mdb database.  Can be modified for many other sources[/COLOR]
    cn.ConnectionString = "C:\myTemp\ADO_DAO_Examples\db1.mdb"
    cn.Open

    
    cmd.ActiveConnection = cn
    cmd.CommandText = "Query1"
    [COLOR="Navy"]Set[/COLOR] param = cmd.CreateParameter("Enter ID", adInteger, adParamInput, 5)
    param.Value = 2
    cmd.Parameters.Append param
    [COLOR="Navy"]Set[/COLOR] param = [COLOR="Navy"]Nothing[/COLOR]

    
    [COLOR="Navy"]Set[/COLOR] rs = cmd.Execute()
    Sheet1.Range("A1:AZ10000").Clear
    Sheet1.Range("A1").CopyFromRecordset rs
                    
                    
My_Exit:
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] rs [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] rs.State [COLOR="Navy"]And[/COLOR] ObjectStateEnum.adStateOpen [COLOR="Navy"]Then[/COLOR]
        rs.Close
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Set[/COLOR] rs = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Set[/COLOR] cmd = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] cn [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] cn.State [COLOR="Navy"]And[/COLOR] ObjectStateEnum.adStateOpen [COLOR="Navy"]Then[/COLOR]
        cn.Close
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Set[/COLOR] cn = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
                    

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Microsoft has some reference material:
Append Method (ADO)

This is some instruction related to stored procedures parameters (SQL server, not Access):
How To Refresh ADO Parameters Collection for a Stored Procedure

 

[Eisasuarez]

Thank you

Is this the same parameter as in the where clause where like have a promt: ? or is this some other type of parameter?

what is this line of code


cmd.CommandText = "Query1" <<Is this a name of atable or SQL string STORED in a table etc?
I know command text can refer to Sql String but that's all i thought it referred to
<what is="" query1?="" this="" a="" name="" of="" table,="" sql="" string?="" i="" know="" you="" could="" have="" an="" string="" like="" .commandtext="strSQL" <font="" color="Navy">Set param = cmd.CreateParameter("Enter ID", adInteger, adParamInput, 5)
param.Value = 2
cmd.Parameters.Append param
If rs.State And ObjectStateEnum.adStateOpen Then
</what>

 

[Eisasuarez]

Ps What if i already had a parameter created in Access? could i use that PARAMETER or do i have to recreate it
and if i had a saved query with a parameter, how can i call that saved query which prompts me with that parameter?