Search code examples
sqlvbams-access64-bitadodb

MS Access - ADODB conection issue - syntax error in FROM clause

I am migrating an App designed in Access 32 bits to Access 64 bits. I have followed all the steps defined in the Microsft website. I have managed to open it and work in most of the macros that App had, but I am struggling with an ADODB connection. I update the Library from previous "Microsoft.Jet.OLEDB.4.0" to "Microsoft.ACE.OLEDB.12.0".

The code open a data base txt file (I include below the structure) and then write it in a Table of the Access database. The issue is when loading the Txt fila into the Recordset. The connection is fine (cnnTXT), but I get the syntax error in the Open step.

Here is the chunck of the code where I am getting the message:

Dim cnnTXT As New ADODB.Connection                                      
Dim rsItfProc As New ADODB.Recordset


cnnTXT.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Forms!Menu_de_Opciones!tb_ruta & "\" & strDirItf & ";Extended Properties=""Text;HDR=no;FMT=Delimited"""


If rsItfProc.State = 1 Then rsItfProc.Close
  Dim file As String
  file = URL_of_the_file 
            
  rsItfProc.Open file, cnnTXT, adOpenStatic, , adCmdTable ---> HERE I AM GETTING THE MESSAGE

End If

The file is like that:

22.11.2022                                             Dynamic List Display                1
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
|Column1   |Column2    |Column3      |Column4          |Valid Until|Valid From|Column5     |
--------------------------------------------------------------------------------------------
|XXXX      |50         |1234567890   |1234567890       |31.12.9999 |01.01.1999|81671454    |
|XXXX      |50         |1234567890   |1234567890       |31.12.9999 |01.01.1999|81671454    |
--------------------------------------------------------------------------------------------

I have tried many things, but I am not an expert on this kind of connections.

I have changed the adCmdTable attribute to adCmdText, I have tried to change the Open to OpenText. Also I have tried to write the SQl sentence as the first attribute but there is no table name, so I am looping around trying things that does not work.

Can anyone tell me if there is something missing or there is a parameter that should not be there? In the 32 bits version it does work.

Regards

Solution

  • You appear to be confusing opening a recordset with opening the file (Connection to the file)

    recordset.Open source, ActiveConnection, options

    Here "source" should be the SQL you want to run. For text files the "table name" is the name of the text file