[RESOLVED] VBA and ADO Connection

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.

:eek2:

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

Quote:

---

Ive tried to write my own

---

post what you have tried so far

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

Code:

---

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

---

Quote:

---

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

Quote:

---

Originally Posted by westconn1

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.)

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

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

Code:

---

  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

---

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.

Quote:

---

What is the "&_" doing in the string

---

probably copied from a web page sample that had line continuations

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

Quote:

---

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

Code:

---

.CommandText = handlerbydept

---

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

ok, Im almost there (i think)

ive now got:

Code:

---

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...

if i understood correctly this is what you want:

Code:

---

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

---

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

Code:

---

Dim team As String
team = departmentcombobox.text

---

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.

Quote:

---

Originally Posted by 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?

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

Quote:

---

Originally Posted by 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

---

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

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.

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

Quote:

---

Originally Posted by 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.

---

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

Code:

---

  ' 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

---

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

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