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,423
    Provided Answers: 8
    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

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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
  •