automation error in access vba

Question

I'm using below code to make connection and execute the query but it not working properly it shows "Auomation error unspecified error" the code is :

On Error GoTo err
    DoCmd.Hourglass (True)

    'For Report file Name
    Dim rs As New ADODB.Recordset
    Dim strReportFileName As String
    Dim rsReportFileName As New ADODB.Recordset
    strReportFileName = ""

    Set con = Application.CurrentProject.Connection

    sSql = ""
    sSql = "SELECT * from tblInputFile as input inner join tblAccountMst as actmaster on input.Account=actmaster.AccountAsPerBOI"

    If rs.State = 1 Then rs.Close
    rs.Open sSql, con, adOpenKeyset, adLockOptimistic **'on this line i get the error and it goes to catch block** 

    If rs.RecordCount > 0 Then
        If FE.FolderExists(Application.CurrentProject.Path & "\Reports\") = False Then
           FE.CreateFolder (Application.CurrentProject.Path & "\Reports\")
        End If
err:
    MsgBox "Error " & err.Description
    DoCmd.Hourglass (False)

please help im beginner in Access VBA. thanks in advance

Andre · Accepted Answer · 2016-11-25 10:57:51Z

1

INPUT is a reserved word in Access SQL, see here or here

Put it in square brackets

sSql = "SELECT * from tblInputFile as [input] inner join tblAccountMst as actmaster on [input].Account=actmaster.AccountAsPerBOI"

or better: use a different alias.

answered Nov 25, 2016 at 10:57

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

automation error in access vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related