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]

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?

My example was in fact a saved query in Access, with a parameter prompt that says "Enter ID" if you run it in Access. So the example above is how you would call such a query.

cmd.CommandText = "Query1" < I know command text can refer to Sql String but that's all i thought it referred to

And the same here. So this is an example of an Access query that "acts" like a stored procedure. It's really just a saved query that has a parameter. In our ADO call we run it by name (the name of the query), and pass it a parameter value, which is exactly how some stored procedures work in other databases.

 

[Eisasuarez]

Thank you

So cmd.command text = "Query1" is the names of a stored procedure

even though a parameter is already created in access called "Enter_ID" i would need to re-create it in VBA
<is this="" a="" name="" of="" atable="" or="" sql="" string="" stored="" in="" table="" etc?
<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) - dont understand the parts in red
param.Value = 2
cmd.Parameters.Append param
If rs.State And ObjectStateEnum.adStateOpen Then</what></is>

thank you and appreciated

 

[xenou]

The additional code is simply the requirement for VBA using ADO. The parameter needs to be defined and that's how you do it. Then the ado object passes it along to the query.

The method you see here is a common one for certain kinds of code objects that use "collections". You create a parameter object. You define some of its properties such as name, data type, and value. Then you "append" it to the parameters collection. You could continue creating more parameters and appending them to the collection if there were more than one parameter, until all of the parameters are defined.


This is a little bit old-fashioned way of closing the recordset:

If Not rs Is Nothing Then
    If rs.State And ObjectStateEnum.adStateOpen Then
        rs.Close
    End If
    Set rs = Nothing
End If

Basically you first check if it is open, then close it, then set it to nothing. An alternative version (without the bitwise AND'ing) is:

If Not rs Is Nothing Then
    If rs.State = 1 Then  '//Open
        rs.Close
    End If
    Set rs = Nothing
End If

But it's really not necessary. ADO will clean itself up even if you exit without closing the recordset. I just like to do so explicitly out of habit.

 

[Eisasuarez]

Hi Xenou,

Firstly i would like to thank you however i am going wrong somewhere. Please advise where i am going wrong.

1) I have a saved query called Query1
2) In the where clause i put this [Enter your User Name] and then pressed SAVE

Now when i go and double click the Quer 1, i always get a promt: saying Enter your user name however i can't seem to find how to view the parameter name or even amend the parameter.

My Question is have actually created a named PARAMETER and if not what is the best way to go about creating the PARAMETER?

I then tried using your code to mirror that so i can input a username but i get an error that says i cant insert, update etc.

This is my Code:

Sub ImportRecordsetACE()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Dim rs As ADODB.Recordset


Dim i As Integer


    
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set cmd = CreateObject("ADODB.Command")
    
    
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"    'Connection to an mdb database.  Can be modified for many other sources
    cn.ConnectionString = "C:\Users\Eisa\Desktop\Excel VBA Examples\VBA Examples\VBA 2010\SampleDatabase3.accdb"
    cn.Open


    
    cmd.ActiveConnection = cn
    cmd.CommandText = "Query1"
    Set param = cmd.CreateParameter("Enter Name", adChar, adParamInput, 50, "Matthew Martell") 'Parameter Name, Parameter Type, Parameter Input, Parameter Size, Parameter Value
    cmd.Parameters.Append param
    Set param = Nothing


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


End Sub

 

[Eisasuarez]

by the way, is this correct

("Enter Name", adChar, adParamInput, 50, "Matthew Martell")
'Parameter Name, Parameter Type, Parameter Input, Parameter Size, Parameter Value

With the createParameter ...Is this creating a brand new parameter or using the existing parameter or does ADO required me to create a brand new parameter regardless if there is any set up in Access?


cmd.Parameters.Append param <<<Still not sure what this code is doing. Trying to understand in plain english

 

[xenou]

Hi,

1) I have a saved query called Query1
2) In the where clause i put this [Enter your User Name] and then pressed SAVE

I think that this means your parameter name is "Enter your User Name", not "Enter Name". You are not creating a parameter in ADO (well, you are but, it is not the parameter in the query, it is the parameter data that you will pass to the query). The parameter must exist in the query first. You must provide the right data that fits the parameter when you call it: the same name, right data type, and the value.

I *always* define parameters explicitly. So I never just put a bracketed phrase like "[Enter your User Name]" into my where clause. I *also* add it to the parameters list, and provide it's datatype. To do this, hit the Parameters button on the Query Design tab. It will open up the parameters dialog. Enter the same thing you put in your where clause. If you put [Enter your User Name] in the where clause, type the same thing in the parameter dialog (with or without the brackets - I always include the brackets). Then select the correct data type for it.

From what I can tell, your query would probably work if you put the same words in the ADO name as you put in the query. But I think it's best to explicitly define the parameter's data type as explained in the previous paragraph.

 

[Eisasuarez]

Thank you , is this correct


("Enter Name", adChar, adParamInput, 50, "Matthew Martell")
'Parameter Name, Parameter Type, Parameter Input, Parameter Size, Parameter Value


With the createParameter ...I guess it's creating a parameter to pass to parameter in access?

ps what if I did not have a parameter created in access? Can I do this in ADO to create it for me so slightly different to it already being created ?




cmd.Parameters.Append param <<how this line work? Append?

 

[Eisasuarez]

Hi,

Is a PARAMETER only created on a saved Query?

 

[xenou]

Thank you , is this correct


("Enter Name", adChar, adParamInput, 50, "Matthew Martell")
'Parameter Name, Parameter Type, Parameter Input, Parameter Size, Parameter Value


With the createParameter ...I guess it's creating a parameter to pass to parameter in access?

ps what if I did not have a parameter created in access? Can I do this in ADO to create it for me so slightly different to it already being created ?

That's looks correct, except that the name you give the parameter must be exactly the same as the way you typed it in Access.

I would recommend you create your queries in Access. Building new queries and changing query definitions with ADO is complicated and generally unnecessary since it is so easy to do right in Access. Your time would be much better spent learning how to do other things. But if you want to do so you must use ADOX. See here:
Microsoft Access tips: ADO Programming Code Examples


P.S.:

Hi,

Is a PARAMETER only created on a saved Query?

Yes.

 

[Eisasuarez]

Hi Thank You

Where am i going wrong with this code..

It gets highlighted where i have highlighted and says Cant be done due to Insert, delete etc..

Sub Connect_With_Parameter()

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


Set ws = Sheets("Sheet1")
UserID = Application.InputBox("Enter User ID", "UserID", Type:=2)


strDBPath = "C:\Users\Eisa\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;"


'strSQL = "SELECT * FROM [users] WHERE [UserID]=@Enter User ID" '<<<@Enter UserID shows that this is a parameter in Access called Enter UserID
'You can also refer to a Saved QUERY like "Query 1"
Set cn = New ADODB.Connection
With cn
.ConnectionString = connection_string
.Open
End With

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = "Query 1"
Set Param = .CreateParameter("@Enter User ID", adChar, adParamInput, 20, UserID)
'Name of Parameter in access
'Data type
'Input
'length of text/size
'Value to be put into parameter<<in my="" case="" from="" inputbox
'All this gets stored into Param Object and says there is a Parameter called Enter UserID and use the value from UserID so Param is what's passed in from UserID
.Parameters.Append Param '<<
<this adds="" the="" parameter="" value="" from="" userid="" to="" in="" strsql="" and="" executes
End With

Set rs = New ADODB.Recordset
rs.Open cmd.execute
ws.Range("A1").CopyFromRecordset rs

CloseRecordset:
'rs.CancelUpdate ' Cancel any updates if there was an error
'rs.Close

End Sub

</this></in>