[RESOLVED] VBA and ADO Connection

[Raanan]

Good afternoon Im hoping you can help.

Im trying to get my head around using ADO connection in VBA for excel and really strulling to get my head around it. I have a stored procedure in Access and a parameter variable.

What I need to have is: (In an excel user form).
When Combobox1 changes - I need the text in combobox 1 to run the Access query. and the returns of this to be a list in combobox2.

Im wondering if someone can help write something quite simple for me to understand so I can manipulate the code to my uses then I can get my head around using this in the future.


FYI:
the stored query name is handlerbydept
Using ACE.OLEDB.12.0
Combobox1 and combobox2 are named just that for now.



Ive tried to write my own but clearly missing various things as i get compile errors etc and im sure im missing very simple parts. Ive looked up other codes but dont get what's where to be able to manipulate it.


Many thanks

[westconn1]

Ive tried to write my own

post what you have tried so far

[Raanan]

Apologies for the delay westconn1.
here's what ive tried.. its raw its horrible and i dare say mnissing vital ingredients

Dim objcommand As ADODB.Command
Set objcommand = New ADODB.Command
Set objconn = "Provider=Microsoft.ACE.OLEDB.12.0; &_ Data Source=feedbacktool.accdb"

With objcommand
.ActiveConnectionConnection = objconn
.CommandType = adCmdStoredProc
.CommandText = handlerbydept
.Prepared = True
.Parameters.Append .CreateParameter("Field1", adVarChar, adParamOutput, 50, departmentcombobox.Text)
Set objRS = objcommand.Execute
End With

[westconn1]

Data Source=feedbacktool.accdb

i am sure this would require the full path to the database

someone else maybe able to find if some other problem in your code

[Raanan]

i am sure this would require the full path to the database

not going to lie the full path is in there i just removed it for security. (probably should have just paraphrased.)

[westconn1]

as i only use ADO, not ACE, you may have to wait for someone else to check your code to see what needs changing

[Arnoutdv]

What is the "&_" doing in the string
Also objconn seems to be a Connection object.
You can not just assign a string to it.

  Dim sDBPath As String, sConnection As String
  Dim oConn As ADODB.Connection
  
  sDBPath = "feedbacktool.accdb"
  sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sDBPath
  Set oConn = New ADODB.Connection
  oConn.Open sConnection

[Raanan]

Arnoutdv, excellent that makes sense I get that bit now.. thank for this.- the connection to the DB works.

Can you help with the rest? I think Ive managed to confuse myself a hell of a lot more.

[westconn1]

What is the "&_" doing in the string

probably copied from a web page sample that had line continuations

[Arnoutdv]

You should start with adding "Option Explicit" as the first line of the module you are working in.

If used, the Option Explicit statement must appear in a module before any procedures.

When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements. If you attempt to use an undeclared variable name, an error occurs atcompile time.

If you don't use the Option Explicit statement, all undeclared variables are of Variant type unless the default type is otherwise specified with a Deftype statement.

This will cause at least an error on

.CommandText = handlerbydept

Because "handlerbydept" is the name of your stored procedure and not a variable.

[Raanan]

ok, Im almost there (i think)

ive now got:

Option Explicit

Sub TESTADO()

Dim sDBPath As String, sConnection As String
Dim oconn As ADODB.Connection

sDBPath = "S:\feedbacktool.accdb"
sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sDBPath
Set oconn = New ADODB.Connection
oconn.Open sConnection
Dim strSQL As String
strSQL = "handlerbydept"
Dim team As String
team = "departmentcombobox.text"
Dim objcommand As ADODB.Command
Set objcommand = New ADODB.Command
With objcommand

.CommandType = adCmdText
.CommandText = strSQL
.Prepared = True
.Parameters.Append .CreateParameter("Field1", adVarChar, adParamOutput, 50, team)

End Sub

Which produces no errors. Im assuming by what ive read that I need to get this to execute the command as to whats in the details above so I can return the results into a combobox.. but no clue

I am also unsure if the details in bold would work...

[IkkeEnGij]

if i understood correctly this is what you want:

Private Sub ComboBox1_Change()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qd As DAO.QueryDef
    
    Set db = OpenDatabase("Path To Your Database")
    Set qd = db.QueryDefs("Name Of Your Query")
    qd.Parameters("Name Of Your Parameter") = ComboBox1.Value
    Set rs = qd.OpenRecordset
    Do While Not rs.EOF
       ComboBox2.AddItem rs.Fields(0)
       rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    qd.Close
    Set qd = Nothing
    db.Close
    Set db = Nothing
End Sub

[Arnoutdv]

If you place quotation marks round a variable name then it will be a literal text.

Dim team As String
team = departmentcombobox.text

[Raanan]

Id like to see if this works. However I get the dreaded run-time "Error 3343: Unrecognized database format ".

Im looking through all the references I need for .Accdb and ill see if i can find a solution. The Microsoft solution doesnt work.

[Raanan]

Id like to see if this works. However I get the dreaded run-time "Error 3343: Unrecognized database format ".

Im looking through all the references I need for .Accdb and ill see if i can find a solution. The Microsoft solution doesnt work.

Looking through all the options it doesnt look like i can do it correctly through DAO. Do you have an ADO alternative?

[Arnoutdv]

DAO?

It should be possible using ADO 2.x and this provider "Provider=Microsoft.ACE.OLEDB.12.0"

https://www.connectionstrings.com/access-2007/
http://www.mrexcel.com/forum/excel-q...ml#post2672484

[Raanan]

DAO?

It should be possible using ADO 2.x and this provider "Provider=Microsoft.ACE.OLEDB.12.0"

https://www.connectionstrings.com/access-2007/
http://www.mrexcel.com/forum/excel-q...ml#post2672484

I was looking at ikkles solution.. Sorry should have quoted.

Still can't get my head around it. Errors all over. I might just have to bite the bullet and do the stuff I need on excel and use formulas

[Arnoutdv]

Maybe start with something simple to get used with how to program in VBA.
Then if you grasp the concept of variables, objects, strings etc etc, just then step into the world of database programming.

[Raanan]

I've done some coding to and from access. Changing previously stored data etc but never used queries and tried to get their return back to excel. Apologies if I've seemed an idiot I just frustrate myself when my heads in code

[TnTinMN]

Good afternoon Im hoping you can help.

Im trying to get my head around using ADO connection in VBA for excel and really strulling to get my head around it. I have a stored procedure in Access and a parameter variable.

Here is an example that you can follow. It is based on two parameters.

   ' add project ref: Microsoft ActiveX DataObjects x.y Library
   Dim conn As New ADODB.Connection
   conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & dbFilePath & "'"
   conn.Open
   
   Dim cmd As New ADODB.Command
   Set cmd.ActiveConnection = conn
   cmd.CommandText = "sp_Procedure1"  ' Name of the procedure
   cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc

   Dim param1 As ADODB.Parameter
   Set param1 = cmd.CreateParameter()
   With param1
'      .Name = "findText"            ' irrelevant, parameters are replaced by order not name
      .Type = adVarWChar
      .Direction = adParamInput
      .Size = 20
      .Value = "hi"
   End With

   Dim param2 As ADODB.Parameter
   Set param2 = cmd.CreateParameter()
   With param2
'      .Name = "intVal"            ' irrelevant, parameters are replaced by order not name
      .Type = adInteger
      .Direction = adParamInput
      .Value = 2
   End With
   
   ' append parameters in order declared in SP
   cmd.Parameters.Append param1
   cmd.Parameters.Append param2
      
   Dim rs As ADODB.Recordset
   Set rs = cmd.Execute
   
   Dim data() As Variant   ' used to print results
   data = rs.GetRows()
         
   conn.Close
   
   ' display the results
   Dim row As Integer
   Dim field As Integer
   Dim firstfield As Integer
   firstfield = LBound(data, 1)
   Dim output As String
   
   For row = LBound(data, 2) To UBound(data, 2)
      output = ""
      For field = firstfield To UBound(data, 1)
         If field > firstfield Then output = output & ", "
         output = output & CStr(IIf(IsNull(data(field, row)), "null", data(field, row)))
      Next field
      Debug.Print output
   Next row

[Raanan]

Thank you TnTinMN,
Ill have a play around with that.. the comments in the code will help me too!

[IkkeEnGij]

Raanan,
glad your problem is solved
however, take a look here where i give a decent explanation about parameter query's
http://www.vbforums.com/showthread.p...-syntax-please post #16
also,do you understand the DAO code ?, i am 100% sure you do (even if you could not get it to work)
do you understand what is going on in the ADO code ?, if you do ...congratulations, then you also know that 80% of it is totally superfluous
if there are no output parameters the command object is not needed, one can use the (implied) execute method of the connection object

psss:
Microsoft Office 14.0 Access Database Engine Object Library (what i reference, because of access 2010 installed)
for access 2007 it probably is 12.0 instead