How to provide dynamic folder and file path (1 Viewer)


Local time
Today, 18:21
Nov 22, 2021

I am facing issue with below code at DoCmd.RunSQL sqlStr this line.
Am getting bug as Syntax error in from clause.

I am importing a text file with below code.
Have provided hard path at sqlStr = sqlStr & " FROM [Text;HDR=Yes;FMT=Delimited;Database=D:\MMM\Personal Documents\MyAccess\TXT to Access].Sample.txt " this line.

I want to make this path as dynamic, I am trying to pass path as variable with MyFilePath but facing issues with this.
Can anyone please help me to make this path selection dynamic. My main moto is to pass path from textbox.
For the moment am passing value through variable MyFilePath.

Sub MyTableImport()
    Dim sqlStr As String
    '+ Delete NewTable
    Dim tableName As String
    tableName = "NewTable"

    If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & tableName & "'")) Then
        DoCmd.SetWarnings False
        DoCmd.Close acTable, tableName, acSaveYes
        DoCmd.DeleteObject acTable = acDefault, tableName
        'Debug.Print "Table" & tableName & "deleted..."
        DoCmd.SetWarnings True
    End If
    '= Delete NewTable
    'Clear data from Revised_Table
    DoCmd.RunSQL "DELETE * FROM Revised_Table"
    sqlStr = "SELECT * INTO NewTable "
    Dim MyFilePath As Variant

    MyFilePath = "D:\MMM\Personal Documents\MyAccess\TXT to Access].Sample.txt"
    'Trying to provide dynamic path instead of below
    'sqlStr = sqlStr & " FROM [Text;HDR=Yes;FMT=Delimited;Database=D:\MMM\Personal Documents\MyAccess\TXT to Access].Sample.txt "
    sqlStr = sqlStr & " FROM [Text;HDR=Yes;FMT=Delimited;Database=MyFilePath"
    DoCmd.SetWarnings False
    DoCmd.RunSQL sqlStr
    DoCmd.SetWarnings True
    Call TableColumnAlter
    Call Add_Column_In_Imported_Table
    Call setPrimaryKey
End Sub

Users who are viewing this thread

Top Bottom