Multiple queries using ADO

[Riverburn]

Hello all,

I'm new here to the community and before I start asking away I want to say hello to all, and hope that I can help out as much as I can.

beforehand, maybe I should inform that I actually am not familiar with VB and even less with access (I was a c# fan and usually went for Visual Studio or Eclipse if Java was used).

so if I make stupid VB mistakes, that's why

My question is about multiple queries.
at the moment whenever I collect data from the database I make a completely new connection, more or less like this:

    Dim conConnection As New ADODB.Connection
    Dim cmdCommand As New ADODB.Command
    Dim rstRecordSet As New ADODB.Recordset
    Dim check_01 As Boolean
 
    conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\FILES\IT_DEV_MGMT.mdb;Mode=Read|Write"
    conConnection.CursorLocation = adUseClient
    conConnection.Open
 
    With cmdCommand
        .ActiveConnection = conConnection
        .CommandText = "SELECT * FROM Employees;"
        .CommandType = adCmdText
    End With
 
    With rstRecordSet
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open cmdCommand
    End With

This works great, easier almost then c#, however now comes my problem. Where in c# I would chase a new query to an already active connection. In VBA - Access, when I want to enter a new query I make a completely new connection. so the above code I repeated now 4 times in a tiny form. and I believe that can be done more efficient.


The reason why I need 4 queries in a tiny form has to do with my inexperience with VB (and SQL). for example, I need to make a list of all active asignments, and a list of all employees.
so I do a

SELECT *
FROM Asignments

and

SELECT *
FROM employees

Because when I combine them (SELECT Asignment.descrip, employees.name FROM asignments, employees) the list of employees is repeated 5 times

Cheers

 

[PNGBill]

I am out of my depth with vba - still trying but in sql you use Where to restrict the result and also the type of join example Inner Join, Outer Join, Left Join.

Maybe get your sql working and then convert it to work in the vba code.

 

[MarkK]

Hello there and welcome to the forum.
Access is always connected to the datasource so you can always get your hands on an open ADODB connection object using ...

dim cnn as ADODB.Connection
set cnn = CurrentProject.AccessConnection

... and since you don't have to open a recordset using an ADODB.Command you could refactor this ...

    Dim conConnection As New ADODB.Connection
    Dim cmdCommand As New ADODB.Command
    Dim rstRecordSet As New ADODB.Recordset
    Dim check_01 As Boolean
 
    conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\FILES\IT_DEV_MGMT.mdb;Mode=Read|Write"
    conConnection.CursorLocation = adUseClient
    conConnection.Open
 
    With cmdCommand
        .ActiveConnection = conConnection
        .CommandText = "SELECT * FROM Employees;"
        .CommandType = adCmdText
    End With
 
    With rstRecordSet
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open cmdCommand
    End With

... to this ...

   Dim rs As New ADODB.Recordset
   rs.Open _
      "SELECT * FROM Employees", _
      CurrentProject.AccessConnection, _
      adOpenStatic, _
      adLockOptimistic

... and CursorLocation is inherited from the connection as adUseClient by default.
Cheers,

 

[Banana]

lagbolt already told you about how Access always has an open connection so there's typically no need to make a new connection. I want to ask though - are you actually binding the form? If you're doing everything using an unbound form, then I would be inclined to say we're missing out much of the benefits of using Access.

PNGBill alluded to using Inner Join. He's actually onto something here. You have syntax like this:

SELECT Asignment.descrip, employees.name FROM asignments, employees) the list of employees is repeated 5 times

That's the old-style join which is actually a cartesian join. Therefore, it's n * m product, but that's rarely desired. Typically we join tables using the ANSI style like this:

SELECT *
FROM employees
JOIN assignments
ON employees.empID = assignments.empID;

which will then return only rows for which value of a given empID (e.g. the primary key of employee table and foreign key for the assignment table) appear in both tables. But maybe you want to see all employees, whether they have any assignments or not:

SELECT *
FROM employees
LEFT JOIN assignments
ON employees.empID = assignments.empID;

That'll give you all employees and for those employees with no assignments, they'll appear once with nulls for the assignment columns.

I hope this helps a bit.

 

[Riverburn]

are you actually binding the form? If you're doing everything using an unbound form, then I would be inclined to say we're missing out much of the benefits of using Access.

to my shame, I'm afraid to admit that I am using an unbound form. I don't use any controlsource or any of that stuff. the reason why is that I never learned to work with access in that way. I know to program, and now I know a bit of VB as well, but it seemed tedious to first learn how controlsource and stuff like that works and then start working.

also, the customer in question is asking a rather specific application in Access, with the possibility to choose assignments and hours on a weekly basis (so that's 7 * #assignments textboxes), and I had no clue how to use a controlsource for that. I did know how to program it thuogh (more or less)

I did let access create a form for me because I was curious how he would do it, and was suprised how easy it was. I tried to play a bit with it, but I was going to slow with little results in how I would reach the end product.

so I started coding

But I want to thank all the people for the advice. I just arrived at work and first I'm going to see how to fill in hours in 42 textboxes per day, and then I'll go over the database code again and clean it up (Yes, I have made a fifth connection like that - I was desperate yesterday )

Cheers, and I'll let you know how I'm doing
once again many many thanks for all the advice

 

[Riverburn]

Little update on the project,

so I inserted the connection code that lagbolt has given me and I have to say, it works like a charm. Wow, does a whole lot for my project, thank you so much LagBolt.

since I've redone all my connection code, I thought that maybe it is time to clean-up my SQL code as well. and I am reminded of all those Joint clauses I had to do in class (maybe it would've been wise to pay more attention to the teacher then play helicopter).

But I'm really grateful Banana, for the queries you've given, they are a lot better then what I have and encourage me to get off my lazy arse and take my Oracle course and start writing some more intelligent SQL code. (trying to get rid of those easy subqueries, as well).

my my, You're good Banana. I'm here looking through my course and there it is: "The AINSI/ISO standard Join Syntax", all explained with emp and dept and deptno and the whole nine yards.

Cheers and once more, thank you

 

[Banana]

take my Oracle course and start writing some more intelligent SQL code.

I'm glad that got you started. You may be interested to know that in SQL world, Oracle is the odd man out when it comes to join syntax. For long time, Oracle did not support the ANSI style joins until quite recently (9i version??). Thus in Oracle documentation/literature you are likely to find syntax just like you used as well as Oracle's special syntax for outer joins (I think it was something like "FROM dept+, emp" for a left outer join?). So you should want to be aware of the SQL differences.

HTH.

 

[Riverburn]

Hmmm, good thing you tell me,

because as you said, the "Outer Join" goes as this in my course:

SELECT e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno (+) = d.deptno
AND
d.deptno IN (30,40)

and I do remember that I had to do stuff like that in class, but goodness did I suck at that. its pretty decently explained though. Nevertheless it's good to know that oracle is the odd one out there and that if this stuff doesn't work I shouldn't panic. The internet is your friend and I'll probably find it explained better and with respect for standards then the stuff here in my oracle course.

my workday is over, gonna take my bike and be gone, and return here tomorrow. once more I want to express my deepest gratitude, and I hope that in the future I'll be able to help some people out.

Cheers