Consulting

Results 1 to 10 of 10

Thread: Take elements from database table to array using VBA

  1. #1

    Take elements from database table to array using VBA

    Hi in order to do that I use this 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
    Attached Images Attached Images

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  3. #3
    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
        s = Application.Count(a)

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

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

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

    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

    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
    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
    Last edited by Aussiebear; 04-12-2023 at 06:44 PM. Reason: Adjusted the code tags

  7. #7
    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.
    SELECT *,  count(Number) as C
    FROM Population
    WHERE Number in (335, 365, 764, 859)
    ORDER BY value_a DESC;

  8. #8
    I wanted to create some custom function and it dosent work when I set strings.
    For Example,
    This works
    Set rst = CurrentDb.CreateQueryDef("Selected_Elements2", " SELECT * FROM  Population ")
    But this dosent
    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
    Last edited by alex009988; 08-10-2019 at 11:11 AM.

  9. #9
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  10. #10
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •