Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2006
    Posts
    48

    Unanswered: Retrieving Data from External Access database

    Hello,

    I am trying to retrieve data from an external Access database. My orginal database is called Bank.mdb with table name tblCheques.

    I archived tblCheques to C:\Archive\BankArchive.mdb in table name tblChequesA.

    From Bank.mdb, I created a form where the user can enter a particular date criteria and once entered, the code is supposed to go through BankArchive.mdb in tblChequesA and retrieve the records, and insert them back into tblCheques.

    Here is my code:

    Private Sub cmdRetrieve_Click()
    Dim conConnector As ADODB.Connection
    Dim strSQL As String

    If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
    MsgBox "Please enter a Date in the format mm/dd/yyyy", vbInformation, "No Information"
    Else
    Set conConnector = New ADODB.Connection
    conConnector.Open "Provider='Microsoft.JET.OLEDB.4.0';" & _
    "Data Source ='C:\Archive\BankArchive.mdb';"
    strSQL = "INSERT INTO tblCheques(ChequeNo, ChequeDate, Payee, Amount, " & _
    "AmountCashed, DatePresented, Status) VALUES IN 'C:\Archive\BankArchive.mdb' " & _
    "(SELECT ChequeNo, ChequeDate, Payee, Amount, AmountCashed, " & _
    "DatePresented, Status FROM tblChequesA WHERE ChequeDate " & _
    "Between [Forms]![frmRetrievePeriod]![StartDate] And [Forms]![frmRetrievePeriod]![EndDate]);"
    CurrentDb.Execute strSQL
    MsgBox "The records are retrieved!"
    'conConnector.Close
    Set conConnector = Nothing
    End If
    End Sub


    However, this code isn't working. I believe there is a error in the sql code. Can anyone help with detecting my faults and help me to retrieve the data back to tblCheques?

    Also, do I have to connect to the database using ADO connection string for something like this?

    With many thanks

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,476
    Provided Answers: 11
    Just Link the table


    The betwwen look wrong

    "Between [Forms]![frmRetrievePeriod]![StartDate] And [Forms]![frmRetrievePeriod]![EndDate]);"

    I would do

    "Between #" & me.StartDate & "# And #" & Me.EndDate & "#) ;"
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    OK...so I goofed the first time. Sorry.

    Again...No. You don't need to make a ADO connection.

    This following code should work for you. I have also added a function named DoesFileExist to check and make sure the Archive .mdb file actually exists (where it's suppose to).

    Code:
    Private Sub cmdRetrieve_Click()
       ' Declare Variables.
       Dim strSQL As String
       Dim DataSource As String
     
       ' Ignore Errors for self processing.
       On Error Resume Next
     
       ' Set The 'DataSource' String variable.
       DataSource = "C:\Archive\BankArchive.mdb"
       
       ' See if the Archive .mdb file actually exists where it's suppose to.
       ' Maybe it was moved or deleted accidently (bummer).
       If DoesFileExist(DataSource) = False Then
          ' Display a message indicating that the Archive file can not be found.
          MsgBox "For some reason we can not locate the required 'BankArchive.mdb' file." & vbCrLf & _
                 "The supplied location is:" & vbCrLf & vbCrLf & DataSource & vbCrLf & vbCrLf & _
                 "This file is required so as to retrieve Archived data.", vbCritical, "Can't Find Archive..."
          ' Exit this Sub without any further processing.
          Exit Sub
       End If
       ' Make sure the Start and End date fields actually contain data.
       If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
          ' Display a message if either date fields contain nothing.
          MsgBox "Please enter a Date in the format mm/dd/yyyy", vbInformation, "No Information"
          ' Exit this Sub without any further processing.
          Exit Sub
       Else
          ' Set the 'strSQL' String variable.
          strSQL = "INSERT INTO tblCheques (ChequeNo, ChequeDate, Payee, Amount, " & _
                   "AmountCashed, DatePresented, Status) SELECT ChequeNo, ChequeDate, Payee, Amount, AmountCashed, " & _
                   "DatePresented, Status FROM tblChequesA IN '" & DataSource & "' WHERE ChequeDate " & _
                   "Between #" & Forms![frmRetrievePeriod].[StartDate] & "# And #" & Forms![frmRetrievePeriod].[EndDate] & "#;"
          
          ' If your Archive table has the identical structure as your actual Database
          ' table then you can shorten the SQL query to what is shown below.
          ' strSQL = "INSERT INTO tblCheques SELECT * FROM tblChequesA IN '" & DataSource & "' WHERE ChequeDate " & _
                   "Between #" & Forms![frmRetrievePeriod].[StartDate] & "# And #" & Forms![frmRetrievePeriod].[EndDate] & "#;"
     
          ' Fire the SQL query.
          CurrentDb.Execute strSQL, dbFailOnError
          ' Trap any Error from 'CurrentDb.Execute'
          If Err <> 0 Then  ' WHOOPS...there was an Error
             ' Indicate there was an Error within the SQL String.
             MsgBox "There is an Error within your SQL String.", vbCritical, "SQL String Error"
             Err.Clear
          Else
             ' Indicate Success.
             MsgBox "The Records requested have been successfully retrieved!", vbInformation, "Archive Retrieval Successfull."
          End If
       End If
    End Sub
     
    Public Function DoesFileExist(PathStrg As String) As Boolean
        Dim a$
        On Error Resume Next
        a$ = Dir(PathStrg, 14)
        If a$ <> "" And Err = 0 Then DoesFileExist = True Else Err.Clear
    End Function
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  4. #4
    Join Date
    Jun 2006
    Posts
    48
    Oh yeah, this works like a charm. Thank you tonnes.

    And I appreciate the extra check scripts too.

    Have a great one!

Posting Permissions

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