VBA attempting first query - ADODB (1 Viewer)

[got_access:]]

Hi All,
I have Access 2016 and am attempting to make a connection to and query a table.

I tried this and got a compile error:
DIM rst as ADODB.Recordset

But this is working:
Dim rst As Object
Set rst = CreateObject("ADODB.Connection")

Any suggestions on what the script would look like from here for querying into a table?

Thanks!

 

[isladogs]

Where is this table? Local or linked or neither?

 

[got_access:]]

Hi, the table is in the file I currently have open in Access.

 

[isladogs]

Then scrap all of that code and run a query or equivalent SQL statement in VBA

 

[got_access:]]

Thanks,
I used the first code because it was provided in a course on Access VBA.
But since it throws an error I can't go any further with it.

Since I'm not familiar with the code that I will need to connect to and query into one of the tables in the file - I wouldn't know code I will need to run a query or equivalent SQL statement in VBA.

However, I am familiar with SQL

 

[isladogs]

Create a query in design view. Add the table you wish to query to the design window. Then select the fields you want to query and run the query.
Hopefully that will get you started.
Obviously you can create far more complex queries than that.

 

[got_access:]]

Ok here is a query

Dim sqlquery As String
sqlquery = "SELECT Count(tblVIDEOS.VideoName) AS CountOfVideoName FROM tblVIDEOS;"

How would I run this in VBA and see the results in the VB editor immediate window?

 

[isladogs]

Suggest you do some reading about using queries in Access

SQL statements for ACTION queries are run from the VBE using
CurrentDB.Execute strSQL or DoCmd.RunSQL strSQL

However, you can't run SELECT SQL statements using VBA.
You can run that statement as a query and if you want you can use DoCmd.OpenQuery "YourQueryName" in VBA

As regards the Immediate window, you can use this code in VBA
Debug.Print DCount("VideoName", "tblVideos")
and the result will be displayed in the Immediate window

 

[got_access:]]

Ok - to back up a little bit - here is the VBA script from my course material on Access 2013 VBA

The script opens the db - specific to a specific table and queries two fields from that table - and then returns the results to the immediate window.

The course I'm reviewing is in video format and the trainer is actually stepping through this script and its running - so at least in the trainer's Access version this script works.

sub LoopProject()
Dim rst as ADODB.Recordset
Set rst = New ADODB.Recordset

'open a recordset based on the projects table
rst.Open "tblProjects", CurrentProject.Connection

'loop through all of the records in the recordset
Do Until rst.EOF

'Print the ProjetID and the ProjectName
Debug.Print rst!ProjectID, rst!ProjectName

'If the ProjectBegindate field is null
'display a message to the user
If IsNull(rst!ProjectBeginDate) then
Debug.Print "Project begin Date contains no value!"
End if
loop
End sub

 

[isladogs]

I'm not suggesting that code won't work.
What I'm saying is that its not the same as what you are asking about and totally unnecessary for your purposes
Looping through a recordset is done row by agonising row so if you have a lot of records its desperately slow.
By contrast queries are effectively done 'all at once' so by comparison are much faster.

Recordset have their uses though personally I prefer DAO rather than ADO.
But counting records in a table isn't a good use for them.

UPDATE:
Having just read that code again, there is a line missing - I've done an equivalent below and highlighted it

Unless you have that line, it will stay on the first record and repeat it endlessly
Also have you added the library reference for ADO as its no longer installed by default
If you don't add the reference, you will get a user type not defined error on the first line: Dim rst As ADODB.Recordset

 

[MajP]

Sorry but unfortunately, your course instructor knows less about Access than 90% of the people on the forum. These guys are "one trick ponies". They have one approach for everything. I would never steer someone to ADODB for a local table, when this can be simply with native Access. Forget what your wanna be instructor said and pay attention to Isladogs.

 

[got_access:]]

Thanks!
Lets say I simply want to learn how to pull values from tables into the VBA environment so I can look at them and do things with them.

Your example here worked: Debug.Print DCount("VideoName", "tblVideos")

It did acquire the correct number.

The VBA course material example didn't work because the first statement threw an error - "Dim rst as ADODB.Recordset"

So apparently there is some difference in my Access system and the system the course trainer is using - such that I can't use the training material example.

Lets go back to the example you provided:
Debug.Print DCount("VideoName", "tblVideos")

Could you provide example script for how I would loop through all of the records in the tblVideos table, and display each "VideoName" value using the Debug.Print command?

Or - since this table also has an ID field, how I would acquire a specific VideoName value - by specifying a specific ID value?

 

[isladogs]

I've updated post #10 but it crossed with yours and MajP's replies.
I think it answers your questions

The reference Microsoft ActiveX Data Objects Recordset 6.0 Library will work

 

[Galaxiom]

Unless you have that line (rst.MoveNext), it will stay on the first record and repeat it endlessly

Pretty sure that one was in Chip Pearson's list of the ten most common errors working with recordsets.

 

[got_access:]]

Yes!
rst.moveNext did work!

Thank you very much!

Can you recommend a source of information I can use to get more details on this?

Also - if I understand another thing you stated - performing ACTION queries like updates to values or inserting values into tables would entail an SQL statement - and then following that with a CurrentDB.Execute strSQL or DoCmd.RunSQL strSQL

I would love a recommended source of further info on that as well.
Perhaps you know of a book that you use as a reference for these processes?

Sincere thanks!

 

[isladogs]

Glad it worked - presumably you added the reference as well.
However don't get stuck on using recordsets unless you need to do so.
They are a small though important part of using Access.
I haven't used an ADO recordset in months though I do use DAO recordsets more often.

There are much better ways of viewing objects in a table.
Normally use a query or form.

As for books, you need to choose something that is right for your level of understanding. What is right for me won't necessarily be right for you

I would suggest you first try the free Access video tutorials by Steve Bishop on You Tube. There are over 100 from beginners to advanced

 

[got_access:]]

Thank very much!
I'll check out Steve Bishop - I believe I've seen a couple of his videos.

My thanks

 

[Galaxiom]

ADO recordsets do have some very good abilities so worth understanding eventually but don't be distracted from the core stuff.