Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    6

    How do I connect to a access database query through code?


    I not sure if I'm wording this right, but how do connect to a query that I have in my access database through VB code.

    I am trying to populate a txtbox on a report that already has another query as its data source. The data for the txtbox that I need to populate is coming from another data source, therefore the only way I could think I could do this was to place code in the report fomat event to populate the txtbox..but I'm not sure of just how to connect to the query

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    You may not need code to do it--in the design view, you can set up a textbox to have a SQL statement for the controlsource.

    What do you need to show in that textbox?

  3. #3
    Join Date
    Jun 2005
    Posts
    6

    How do I connect to a access database query through code?

    The report has several grouping; SB_Code, Naic_Code, SourceID_Code
    Under the sourceid_code heading I have a total field and then the % of actual data (by month) / by the total of data over a five year period.

    Example: ---------------------------- Approve ---Year----- Jan----- Feb
    SB_Code: ABCDEF ------------------- qty -------Total----- %------ %
    Naic_Code: 123
    SourceID_Code: 999999 ----------- ????-----3614.92--7.82%--- 7.99%


    In the approve qty text box I need to display a sum of the entire approved amount, over a five year period, within SB_Codes, Naic_Codes, and SourceID_Code. The information for this box is coming from a different table which I can’t mix with the first query; because when I do it duplicate the amounts on the first query. The sql code for both query is long because I have to join several tables to get the results.

  4. #4
    Join Date
    Jun 2005
    Posts
    6

    Need to connect to a access through code? Please help!!!

    Hi MatthewsPatrick, I tried putting SQl code in the text box but it didn't work. So now I'm trying to connect with to my local db through ADODB. here is the code I'm using

    Public Function GetApprovedSource()
    Dim dblCUTotal As Double

    On Error GoTo ConnectionError

    Dim db As ADODB.Connection
    'Dim db As Database
    Dim rst As ADODB.Recordset
    Dim SelectCU As String


    dblCUAmount = 0
    'Set db = CurrentDb
    Set db = CurrentProject.AccessConnection

    SelectCU = "select tblTotalApprovedCU_All.SourceTotal " _
    & "from tblTotalApprovedCU_All " _
    & "where tblTotalApprovedCU_All.SB_Code = " & strSBCode _
    & " and tblTotalApprovedCU_All.NAICS_3digit = " & intNAICSCD _
    & " and tblTotalApprovedCU_All.sourceTypeLID = " & intSourceID

    'Set rst = db.OpenRecordset(SelectCU, dbOpenSnapshot)
    Set rst = New ADODB.Recordset
    rst.Open SelectCU, dbOpenSnapshot
    Do Until rst.EOF
    If rst!SourceTotal > 0 Then
    dblCUAmount = dblCUAmount + rst!SourceTotal
    rst.MoveNext
    End If
    Loop
    Set rst = Nothing
    Set db = Nothing
    Exit Function

    ConnectionError:

    MsgBox "There was an error connection to the database. " & Chr(13) _
    & Err.Number & "," & Err.Description

    End Function

    I get as far as the rst.open statement and then I get an error 3001
    "Runtime error '3001':
    The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another. "

    I not sure what to do at this point, can you help?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. connect from access to sql
    By kathyc in forum Programming
    Replies: 0
    Last Post: 09-28-2008, 12:50 PM
  2. code help for updat query
    By Grant in forum Queries
    Replies: 1
    Last Post: 01-31-2008, 05:53 AM
  3. very slow when connect to access 2000
    By pureland in forum Access
    Replies: 0
    Last Post: 10-23-2007, 05:42 AM
  4. Using SELECT query within a VBA code in Access
    By championss in forum Programming
    Replies: 4
    Last Post: 10-23-2006, 05:50 PM
  5. Replies: 0
    Last Post: 11-28-2005, 01:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums