CODEWITHSUNDEEP
sqlms-access
Trial4life
Trial4life

Reputation: 141

MS Access - INTO clause containing square brackets in file path

I have this simple SQL query that creates a new table in another MS Access database file, getting the data from a table in the current MS Access file where the query is running.

SELECT * INTO [<FILE PATH>].NEWTABLE FROM TABLE

However, the file path contains square brackets, creating conflicts while reading the file path (it ends after the first closed square bracket that is occurring in the file path).

How can avoid this conflict in the file path?

Upvotes: 0

Views: 129

Answers (1)

ValNik
ValNik

Reputation: 5593

I think, square brackets and other special symbols in file path or name is problem.
If not impossible change that path, try reverse task.

Function Test2()
Dim filePath As String
Dim sqlExpr As String
Dim db As DAO.Database
Dim currDbPath As String
    currDbPath = CurrentDb.Name
    filePath = "<file path with [ and].accdb>"
    Set db = OpenDatabase(filePath)
    sqlExpr = "SELECT * INTO NEWTABLE4  FROM [" & currDbPath & "].tbTest1"
    db.Execute (sqlExpr)
    db.Close
    Set db = Nothing
End Function

In case where both files contain square brackets, use TransferDatabase method)

Function test5()
Dim sourceTableName As String
Dim destTableName As String
Dim destDbPath As String
Dim sqlExpr As String
Dim db As DAO.Database
    destDbPath = "<file path with [ and].accdb>"
    sourceTableName = "tbTest1"
    destTableName = "NewTable7"
    DoCmd.TransferDatabase acExport, "Microsoft Access", _
        destDbPath, acTable _
       , sourceTableName, sourceTableName
       ' case 1 - when table names different  - created link
       ', sourceTableName, destTableName 
    Set db = OpenDatabase(destDbPath)

    sqlExpr = "SELECT * INTO NEWTABLE  FROM " & sourceTablename 
    ' or in case 1  - linked table
    'sqlExpr = "SELECT * INTO NEWTABLE  FROM " & destTablename 

    db.Execute (sqlExpr)
    ' delete table destTableName, if necessary
    db.Close
    Set db = Nothing

End Function

Edit example. In 2nd case,if parameters sourceTableName, destTableName is the same, TransferDatabase creates copy of table in destination base. From this table, or from linked table in 1 case, you can copy to target table.
As you originally intended

SELECT * INTO NEWTABLE FROM destTableName

Then delete or not this (temporary) table or link.

From MS Access doc:If you import a table from another Access database that's a linked table in that database, it will still be linked after you import it. That is, the link is imported, not the table itself.
This feature should be taken into account when using the function TransferDatabase.
@Trial4life says "If I use DoCmd.TransferDatabase, then it creates a linked table in the backup file, while I need a normal table". Most likely, he does not have the source table itself in the database, but a link to it.
In this case SELECT * INTO NEWTABLE FROM destTableName is necessarily.

Upvotes: 1

Related Questions