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]

These lines aren't right:

Set rs = New ADODB.Recordset
rs.Open cmd.execute

Just:

Set rs = cmd.execute()

Set Param = .CreateParameter("@Enter User ID", adChar, adParamInput, 20, UserID)

note that unless you actually typed an @ as part of your parameter in Access, we don't use @ signs in Access.

 

[Eisasuarez]

Hi

do i need.execute with brackets?

So if had a Parameter in an SQL string, how do i put the parameter in where clause so it recognises that this is a PARAMETER?

I.e sql SELECT * FROM USERS WHERE USERID = THIS IS A PARAMETER

 

[xenou]

I should have said:

Set rs = New ADODB.Recordset
rs.Open cmd.execute

Should be:

Set rs = New ADODB.Recordset
Set rs = cmd.execute()

You do need to create the recordset object first. The command object returns a recordset when you provide it with a select query.

Hi

do i need.execute with brackets?

So if had a Parameter in an SQL string, how do i put the parameter in where clause so it recognises that this is a PARAMETER?

I.e sql SELECT * FROM USERS WHERE USERID = THIS IS A PARAMETER

You should know by now. The parameter must be created in a saved query. You do not use a sql string. You use the name of the stored query only.

 

[xenou]

This is what a true parameterized query looks like in Access. You can ascertain this for yourself by creating a query in design view, using the parameters dialog box, and then inspecting the SQL that is generated:

PARAMETERS [Enter Account Number] Text ( 255 );
SELECT GLAccts.GLAccountDescription
FROM GLAccts
WHERE (((GLAccts.GLAccount)=[Enter Account Number]));

(You do this in Access. Not in your ADO code.)

 

[Eisasuarez]

Hi

Thank You


Set rs = cmd.execute() Do we need the brackets?

This line sets rs : Set rs = New ADODB.Recordset

What about this line
Set rs = cmd.execute() with brackets?

what do you mean by this

The command object returns a recordset when you provide it with a select query.

I have gathered that Parameters are only used with Stored Procedures but not sure about this part as we are setting rs again

Set rs = cmd.execute() with brackets?

 

[xenou]

This creates a recordset object. It is just a thing that exists in code. But it has no records.

Set rs = New Adodb.Recordset

This runs a command to actually get some records and return them as your recordset.

Set rs = cmd.execute()

The brackets are not brackets . They are parentheses. You need these when calling functions in VBA.



Don't worry about syntax too much. You just get used to it after a while.

 

[Eisasuarez]

Cmd.execute i didnt know that was a function as in the pass within a with statement i just did . Execute

i.e

with cmd
.commandtext = sqlstr
.execute
end with

and

rs.open sqlstr

 

[xenou]

with cmd
.commandtext = sqlstr
.execute
end with

and

rs.open sqlstr

You are not following the example code.

 

[Eisasuarez]

Hi Xenou

thank you for your patience

what do you mean by this

The command object returns a recordset when you provide it with a select query?

is this correct

If i amusing an sql string to popoulate recordsrt then i could do
SET RS = NEW ADODB.RECORDSET
rs.open sqlstring, connection so set is not required

if i am using the command object to populate the recordset then

i would need to SET RS = cmd.execute() so i would need to use SET

so in theory these 2 statements open up a recordset in memory even though with the first 1 i explicitly use the word open


SET RS = NEW ADODB.RECORDSET
rs.open sqlstring, connection <<this uses open

and the below are same ie both open recordset in memory

SET RS = cmd.execute() << this does not use open

but they but are open so i cant do

rs.open with this as it is already open
SET RS = cmd.execute()

 

[xenou]

You are confusing yourself by trying to do both at the same time.

Just keep it simple. EITHER use a COMMAND object to get the recordset, OR use a RECORDSET object to get the recordset. Don't try to do both. They are both different in how you write the code, but in the end you get the same result.

At this point you should be answering some of your own questions by testing it out and seeing if it works.