Limiting size of Access recordset 
Author Message
 Limiting size of Access recordset

As a learning exercise with practical value, I want to build a VB6 front-end
to an Access 2000 database, for several (10-15) users to access via the
network. I have no problems connecting to the database via ODBC, but I would
like to limit the number of records displayed using a couple of combo boxes
(clients and project_number).

I can get the clients combo box to work using ADO, but am struggling to find
out how to get the second one to work - I have a SQL string that
incorporates the return from 'clients', but this gives me parameter required
error messages.

Any ideas much appreciated.

Rod



Wed, 12 Feb 2003 12:33:03 GMT  
 Limiting size of Access recordset
Hi Rod. You don't give any examples of your SQL or code, so we're thrashing
about in the dark. But the 'parameters required' message suggests, among
other things, that your SQL is not being interpreted correctly. Try placing
brackets around key phrases. Or post it here, so we can look at it.


Quote:
>As a learning exercise with practical value, I want to build a VB6
front-end
>to an Access 2000 database, for several (10-15) users to access via the
>network. I have no problems connecting to the database via ODBC, but I
would
>like to limit the number of records displayed using a couple of combo boxes
>(clients and project_number).

>I can get the clients combo box to work using ADO, but am struggling to
find
>out how to get the second one to work - I have a SQL string that
>incorporates the return from 'clients', but this gives me parameter
required
>error messages.

>Any ideas much appreciated.

>Rod



Wed, 12 Feb 2003 03:00:00 GMT  
 Limiting size of Access recordset
Thanks, I actually worked it out by myself - eventually!

The code for the form at the current stage of development is as follows:
Private Sub Form_Load()
    Dim fSQL As String
    fSQL = "SELECT * FROM Projects"
    Adodc1.RecordSource = fSQL
    Adodc1.Refresh
End Sub

Private Sub cboProduct_Click(Area As Integer)
    Dim strSQL As String
    strSQL = "Select projects.proj_num "
    strSQL = strSQL & "from products inner join projects "
    strSQL = strSQL & "on products.product_id=projects.product_id "
    strSQL = strSQL & "where products.product = " & Chr(39)
    strSQL = strSQL & Me!cboProduct
    strSQL = strSQL & Chr(39)
    Adodc1.RecordSource = strSQL
    Adodc1.Refresh
    If Adodc1.Recordset.RecordCount <> 0 Then
        Me!cboPassport = Adodc1.Recordset.Fields(0)
    Else
        MsgBox "There are no Projects associated with this Product! ",
vbOKOnly
        Exit Sub
    End If

End Sub

Private Sub cboPassport_Click(Area As Integer)
    On Error Resume Next
    Dim pSQL As String
    pSQL = "SELECT Projects.proj_num, "
    pSQL = pSQL & "Projects.proj_description, " ' Text 0
    pSQL = pSQL & "Projects.target_date, "      ' Text 1
    pSQL = pSQL & "ProjType.proj_type, "        ' Text 2
    pSQL = pSQL & "Clients.client_name, "       ' Text 3
    pSQL = pSQL & "writer.lastname, "           ' Text 4
    pSQL = pSQL & "authors.lastname, "          ' Text 5
    pSQL = pSQL & "Managers.lastname,"          ' Text 6
    pSQL = pSQL & "Projects.MinSales, "         ' Text 7
    pSQL = pSQL & "Projects.MaxSales "          ' Text 8
    pSQL = pSQL & "FROM Managers, ProjType, authors, writer, "
    pSQL = pSQL & "projects INNER JOIN (Products INNER JOIN clients "
    pSQL = pSQL & "on products.client_id=clients.client_id) "
    pSQL = pSQL & "ON products.product_id=projects.product_id "
    pSQL = pSQL & "where products.product= " & Chr(39)
    pSQL = pSQL & Me!cboProduct
    pSQL = pSQL & Chr(39) & " AND projects.proj_num="
    pSQL = pSQL & Me!cboPassport
    pSQL = pSQL & " AND (projects.writer_id=writer.writer_id) "
    pSQL = pSQL & "AND (projects.author_id=authors.author_id) "
    pSQL = pSQL & "AND (ProjType.proj_type_id=Projects.proj_type_id) "
    pSQL = pSQL & "AND (Managers.manager_id=Projects.manager_id)"
    Adodc1.RecordSource = pSQL
    Adodc1.Refresh
    Text1(0).Text = Adodc1.Recordset.Fields(1)
    Text1(1).Text = Adodc1.Recordset.Fields(2)
.
.
.
End Sub

Now to move on to being able to use combo boxes where there could be
multiple choices, and then to be able to update the original record - ain't
learning fun!

Rod


Quote:
> Hi Rod. You don't give any examples of your SQL or code, so we're
thrashing
> about in the dark. But the 'parameters required' message suggests, among
> other things, that your SQL is not being interpreted correctly. Try
placing
> brackets around key phrases. Or post it here, so we can look at it.



> >As a learning exercise with practical value, I want to build a VB6
> front-end
> >to an Access 2000 database, for several (10-15) users to access via the
> >network. I have no problems connecting to the database via ODBC, but I
> would
> >like to limit the number of records displayed using a couple of combo
boxes
> >(clients and project_number).

> >I can get the clients combo box to work using ADO, but am struggling to
> find
> >out how to get the second one to work - I have a SQL string that
> >incorporates the return from 'clients', but this gives me parameter
> required
> >error messages.

> >Any ideas much appreciated.

> >Rod



Sat, 15 Feb 2003 12:29:55 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. ADO Createable recordset size limits?

2. ADO Createable recordset size limits?

3. Limiting the size of a recordset

4. Access 97 Size limit

5. Access file size limit

6. Known user and file size limit 4 Access?

7. Size limit of Access table

8. Access size limit solution?

9. Size of Controls limited by Form Size ?

10. Size of Controls limited by Form Size ?

11. String Size Limit in Textbox and Listbox

12. Output To File Size Limits

 

 
Powered by phpBB® Forum Software