Take elements from database table to array using VBA

Hi in order to do that I use this code

Code:

---

Sub test()
Dim rstData As DAO.Recordset
Dim a1 As Variant
Set rstData = CurrentDb.OpenRecordset("select value_a from Population")
rstData.MoveLast
rstData.MoveFirst
a1 = rstData.GetRows(rstData.RecordCount)
Debug.Print Join(a1, " ")
End Sub

---

But I get error Invalid Procedure call or argument.
Whta's wrong with my code?
EDIT1 Seems this code gives two dementional array, kinda a1(0, 0)
Is it possible to get one dementional array straight away? Without looping

You do not actually have any error trapping, which might identify where the error is occurring.
In the debug print you use Join( but you have not dimensioned Join to tell Access what it is.
You should also redim the A1 Variant before using it.

Thenk you for your reply. I have found a more or less way to take an array and found out that excel vba functions dosent work in access.
Is there a functions for access to find max/min elements of array and its length?
For example

Code:

---

    s = Application.Count(a)

---

You can use Ubound and Lbound to establish how many elements there are in the Array.
However I am not sure why you are using VBA & an Array to do what you are doing, or what you trying to achieve.
The one thing that you need to forget when working with Access is how things are done in Excel.
Access is designed for data handling and although VBA Recordsets are extremely powerful so are SQL queries.

I want to use random sampling and sampling proportional to the size. So it uses some array algorimn.
I have some result array of sampled elements and I want it make a SQL query of SELECT...
As it's imposible to do some things as declare variables, array stuff, etc on JetSQL so I need to conbine SQL with VBA
Now I cant even to do this simple query

Code:

---

Sub test2()
CurrentDb.Execute "SELECT number,value_a from Population where value_a>=10000000"
End Sub

---

get the error runtime error 3065.
My question is it possible to SELECT in sql by VBA array?
So I have in vba some array 1,2,3. And I want to SELECT in SQL 1,2,3 elements of number column, value_a.

You should assign the Select SQL to a recordset to run it.
I do not think it is possible to just set the SQL to the array, you could set it to the individual Array elements using an "Or" statement or and "And" statement.
The SQL and VBA recordset are extremely particular about the Syntax of the SQL statement.
To get it right it is ussually best to break it up on separate lines so that you can build it up when testing.
Here is two examples of the VBA to open a recordset with one and two where statement.

Code:

---

Dim SQL As String
On Error GoTo Eventerror
zeroes = "000000": year = Format(Date, "yyyy"): month = Format(Date, "mm")
SQL = "SELECT Que_CallNumber.* " & _
"FROM Que_CallNumber " & _
"WHERE DealerCode = " & Me.Dealercode
Set rs = CurrentDb.OpenRecordset(SQL)
Exit Sub
Eventerror:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Descri

---

ption

this for more than one

Code:

---

SQL = "SELECT LabourRates.* " & _
  "FROM LabourRates " & _
  "WHERE Date1 <= #" & Format(Me.Date, "mm/dd/yyyy") & "#" & _
  "And Date2 > #" & Format(Me.Date, "mm/dd/yyyy") & "#"
  Set rs = CurrentDb.OpenRecordset(SQL)
  er = rs.EndUserRate
  rs.Close
  Set rs = Nothing

---

I also found this code for working with an SQL Server

Code:

---

Dim rsCustomer As ADODB.Recordset
Set rsCustomer = New ADODB.Recordset
rsCustomer.Open "SELECT BI FROM FichaCandidatura WHERE BI ='" & Me.BI & "'", CurrentProject.Connection, adOpenStatic, adLockOptimistic
txtCustomerNo = rsCustomer.Fields("BI")
rsCustomer.Close

---

Well, thank you for your reply. I've found a way how to select and insert into values from vba array.
Now I want to add pseudo column on select query which counts lines/rows. so that 1,2,3,4,5....
If it's impossible for access, I can use for loop in vba. But in this case, how to insert these values into pseudo column?
I have this code generated by vba.

Code:

---

SELECT *,  count(Number) as C
FROM Population
WHERE Number in (335, 365, 764, 859)
ORDER BY value_a DESC;

---

I wanted to create some custom function and it dosent work when I set strings.
For Example,
This works

Code:

---

Set rst = CurrentDb.CreateQueryDef("Selected_Elements2", " SELECT * FROM  Population ")

---

But this dosent

Code:

---

Dim zzz  As String
zzz = "Population"
Set rst = CurrentDb.CreateQueryDef("Selected_Elements2", " SELECT * FROM  " & zzz & ")

---

What's wrong with the second one?
EDIT 2// Solved the second problem

Well done on working them out, as you can see the Syntax is critical with VBA created SQL.
The Access version for some bizarre reason does not have a "Running count" function, just a total count function.

You can count using SQL with a Module.
You can also create the running count if you display the SQL in a Form or Report.
I will post some examples tomorrow.

Here are 2 versions of Running Count Queries.
The first is a Query with a VBA module that returns the Running Sum. Because it uses a Public Integer called runnungsum to hold the running sum it has to run with VBA that resets the Public Integer each time otherwise each time you run it without closing the database the running sum will start where the previous run left off.
The second Query uses a DSum Function and an Alias combined with a precount query to achive the same thing.

There are other methods, for instance using a dummy table with an Autonumber to create the runnng count, but the table would have to be deleted after each run.

PLease note that Access Query SQL does not transfer directly to VBA created SQL due to syntax differences.