Text File Specification Error

[damainkilla]

I have an Access database that is linked to csv text files, and I've written 5 simple queries (all involving an inner join and 2 where conditions). I've also created a macro that refreshes all these queries that right now are in a single Excel worksheet. The stand-alone macro works fine, but when I'm trying to incorporate it into a much bigger Excel add-in for my workplace it's giving me this error - "The text file specification '.... ' does not exist. You cannot import, export, or link using the specification." I've done some searches and I couldn't really understand what the underlying issue behind this error was about. So although I technically can use the stand-alone macro to do the job (at least for now), I don't think it's the best solution. Any simple explanation(s) to this error message or inputs to solving this problem would be super helpful!

I also experienced this error when I first "imported" the data from Access into Excel as a table. My workaround was saving and then closing the Excel workbook once I've successfully imported the data into Excel.

Here's the code to the stand-alone macro

Sub RefreshTable()

On Error GoTo ErrHandler
Dim ConnectionName As String
Dim Connection_Array() As String
Dim i As Integer
Dim MyConnection As WorkbookConnection
Dim DBSource As String
Dim ConnectionString As String


DBSource = "" 'full path to db source


For i = 1 To ActiveWorkbook.Connections.Count


    ConnectionName = ActiveWorkbook.Connections(i).Name
    Set MyConnection = ActiveWorkbook.Connections(i)
    ConnectionString = MyConnection.OLEDBConnection.Connection
    
    ' Break the connection string into its constituent parts
    Connection_Array = Split(ConnectionString, ";")
    
    ' Replace the data source
    Connection_Array(3) = "Data Source =" & DBSource


    ' Put the connection string back together
    ConnectionString = Join(Connection_Array, ";")
    
    ' Change connection string of table
    MyConnection.OLEDBConnection.Connection = ConnectionString
    
    'Refresh data
    ActiveWorkbook.Connections(i).Refresh
    
Next


ErrHandler:
  Dim errMsg As String
  errMsg = "Error number: " & str(err.Number) & vbNewLine & _
           "Source: " & err.Source & vbNewLine & _
           "Description: " & err.Description
  Debug.Print errMsg


End Sub

 

[xenou]

I'm a little confused by this. You have data in text files, with queries in Excel? How is this in an Access database and what does Access have to do with it?

I would personally remove the error handler from the code, then investigate the point where the code fails - which line is it on? What are the variable values at that moment in time?

The code, as it stands, looks strange to me since it seems to remove a connection db source but replaces it with nothing, so that seems like it would fail to me.

DBSource = "" 'full path to db source

    Connection_Array(3) = "Data Source =" & DBSource

 

[damainkilla]

I'm a little confused by this. You have data in text files, with queries in Excel? How is this in an Access database and what does Access have to do with it?

I would personally remove the error handler from the code, then investigate the point where the code fails - which line is it on? What are the variable values at that moment in time?

The code, as it stands, looks strange to me since it seems to remove a connection db source but replaces it with nothing, so that seems like it would fail to me.

DBSource = "" 'full path to db source

    Connection_Array(3) = "Data Source =" & DBSource

So I have the data in text files, the tables in Access I'm using are linked to those text files. I'm having the results out in Excel because this is supposed to be a part of a huge VBA add-in. This add-in tests different things depending on whichever client the user is using this for, and so every client gets their own Access database.

I have the connection db source set to a directory provided by the user through an InputBox, but to have this here I thought there's no point including it so I set it to blank.

The line it fails on is the last line, where I'm refreshing the table(s), primarily the second table/connection in my Excel sheet.

 

[xenou]

I have the connection db source set to a directory provided by the user through an InputBox, but to have this here I thought there's no point including it so I set it to blank.

I'm pretty sure the connection db source cannot be blank. It's like saying "connect to a blank db". So that appears to be the problem here.

 

[damainkilla]

I'm pretty sure the connection db source cannot be blank. It's like saying "connect to a blank db". So that appears to be the problem here.

Yeah I've realized it can't be blank. The reason I'm setting it blank here because it adds 0 value to people testing the code if they wanted

 

[xenou]

Okay. I thought *you* were testing since you had these errors you were trying to figure out!