Solved error on auto refresh linked tables with VBA code

[Boromyr]

Hi. I'm trying to automate the updating of linked tables in a database in case of moving the main DB.
For the .xlsx files, the code works.
For the .csv files, the code give me error "3044 - 'C:\correctpath\Invoice_New.csv' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server onto which the file resides.
The path is correct....and for the .xlsx it work....someone can help to fix it ?
Thanks & sorry for the bad english

Dim Cpath As String
    Cpath = CurrentProject.Path
  
    Dim File
    Set oFSO1 = CreateObject("Scripting.FileSystemObject")
    File = Cpath & "\utility\Path.txt"
    Set oTXT = oFSO1.OpenTextFile(File, 1, True)
    Line = oTXT.ReadLine
  
    If Line <> Cpath Then
        Dim tdf As DAO.TableDef
        Dim Db As DAO.Database
        
        Set Db = CurrentDb()
        
        Set tdf = Db.TableDefs("DayMonth")
        tdf.Connect = "Excel 12.0 Xml;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=" & Cpath & "\utility\DayMonth.xlsx"
        tdf.RefreshLink
        
        Set tdf = Db.TableDefs("Racc_Rating_Text")
        tdf.Connect = "Excel 12.0 Xml;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=" & percorso & "\utility\Racc Rating text.xlsx"
        tdf.RefreshLink
        
        Set tdf = Db.TableDefs("Invoice_New")
        tdf.Connect = "Text;HDR=YES;FMT=TabDelimited;DATABASE=" & percorso & "\input\Invoice_New.csv"
        tdf.RefreshLink

 

[theDBguy]

Hi. Welcome to AWF!

Based on the error message and the code texts, it seems the use of a space character in the file name is giving you a problem. Try removing it.

 

[Boromyr]

Hi. Welcome to AWF!

Based on the error message and the code texts, it seems the use of a space character in the file name is giving you a problem. Try removing it.

Thx for the welcome

In example i poste only 2 xlsx and 1 csv to show VB code, but the error is not on the file named with space, first & second are .xlsx and works.
The problem is in the third.
In the origibal DB there are 5 .xlsx that workk perfectly, and 5 .csv that cause all the same problems.

 

[theDBguy]

Thx for the welcome

In example i poste only 2 xlsx and 1 csv to show VB code, but the error is not on the file named with space, first & second are .xlsx and works.
The problem is in the third.
In the origibal DB there are 5 .xlsx that workk perfectly, and 5 .csv that cause all the same problems.

Hi. Sorry. My eyes played a trick on me. Must be this small screen I'm using at the moment.

Sent from phone...

 

[Isaac]

Can you try running this code and tell me what happens?

I don't think you posted "all" your code, so please just run your code as normal, but replace the snippet you showed us, with this snippet entirely:

Dim Cpath As String
    Cpath = CurrentProject.Path
 
    Dim File
    Set oFSO1 = CreateObject("Scripting.FileSystemObject")
    File = Cpath & "\utility\Path.txt"
    Set oTXT = oFSO1.OpenTextFile(File, 1, True)
    Line = oTXT.ReadLine
 
    If Line <> Cpath Then
        Dim tdf As DAO.TableDef
        Dim Db As DAO.Database
        
        Set Db = CurrentDb()
        
        Set tdf = Db.TableDefs("DayMonth")
        tdf.Connect = "Excel 12.0 Xml;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=" & Cpath & "\utility\DayMonth.xlsx"
        tdf.RefreshLink
        
        Set tdf = Db.TableDefs("Racc_Rating_Text")
        tdf.Connect = "Excel 12.0 Xml;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=" & percorso & "\utility\Racc Rating text.xlsx"
        tdf.RefreshLink
        
        Set tdf = Db.TableDefs("Invoice_New")
        msgbox dir(percorso & "\input\Invoice_New.csv)
        tdf.Connect = "Text;HDR=YES;FMT=TabDelimited;DATABASE=" & percorso & "\input\Invoice_New.csv"
        tdf.RefreshLink

 

[Gasman]

Thx for the welcome

In example i poste only 2 xlsx and 1 csv to show VB code, but the error is not on the file named with space, first & second are .xlsx and works.
The problem is in the third.
In the origibal DB there are 5 .xlsx that workk perfectly, and 5 .csv that cause all the same problems.

However those first two files have completely different paths?

 

[Solo712]

I have noticed that the subfolder for the excel files is "utility" but for the cvs file it is "input". Do you have the csv's in the correct directory?

Best,
Jiri

 

[deletedT]

Can you try running this code and tell me what happens?

I don't think you posted "all" your code, so please just run your code as normal, but replace the snippet you showed us, with this snippet entirely:

I was thinking exactly the same. What is that percorso variable?

 

[Isaac]

I was thinking exactly the same. What is that percorso variable?

Yes...after so many posts where someone feels "sure" that the path is correct, I was wanting to put forth the gentlest possible version of "prove it".
Since 9 times out of 10, the answer comes back, Oops, the path really was wrong. Figured that message box would answer the question easier than walking someone through breakpoints and immediate windows, for a change.

 

[Boromyr]

Sorry guys.

I tried to translate from italian to make it easy, but it wasn't a good idea...

This is the complete code, the error, the debug, the paths and the result on linked table, that work for the .xlsx file but not for the .csv

Private Sub TastoSI_Click()

    'On Error GoTo ErrAggTab
    Dim percorso As String
    percorso = CurrentProject.Path
    'Trovo in che cartella è stato copiato il QC DT
  
    Dim File
    Set oFSO1 = CreateObject("Scripting.FileSystemObject")
    File = percorso & "\utilità\Percorso.txt"
    Set oTXT = oFSO1.OpenTextFile(File, 1, True)
    Line = oTXT.ReadLine
    'Controllo il percorso in cui è inserito il DB
  
    If Line <> percorso Then
        Dim tdf As DAO.TableDef
        Dim Db As DAO.Database
        Dim tname As String
        'Set Db = CodeDb
        Set Db = CurrentDb()
        Set tdf = Db.TableDefs("GiorniMese")
        tdf.Connect = "Excel 12.0 Xml;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=" & percorso & "\utilità\Mese_Giorni.xlsx"
        tdf.RefreshLink
        Set tdf = Db.TableDefs("Obiettivi_QC")
        tdf.Connect = "Excel 12.0 Xml;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=" & percorso & "\input\OBIETTIVI QC.xlsx"
        tdf.RefreshLink
        Set tdf = Db.TableDefs("Racc_Rating_Testo")
        tdf.Connect = "Excel 12.0 Xml;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=" & percorso & "\utilità\Raccordo Rating testo.xlsx"
        tdf.RefreshLink
        Set tdf = Db.TableDefs("Tabella_Bonis")
        tdf.Connect = "Excel 12.0 Xml;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=" & percorso & "\utilità\Tabella_Bonis.xlsx"
        tdf.RefreshLink
        Set tdf = Db.TableDefs("Fatture_Scadute")
        tdf.Connect = "Text;HDR=YES;FMT=TabDelimited;DATABASE=" & percorso & "\input\Fatture_Scadute.csv"
        tdf.RefreshLink
        Set tdf = Db.TableDefs("Fidi_Revoca")
        tdf.Connect = "Text;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & percorso & "\input\Fidi_Revoca.csv"
        tdf.RefreshLink
        Set tdf = Db.TableDefs("Sconfini_PD")
        tdf.Connect = "Text;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & percorso & "\input\Sconfini_PD.csv"
        tdf.RefreshLink
        Set tdf = Db.TableDefs("Totale_PD")
        tdf.Connect = "Text;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & percorso & "\input\Totale_PD.csv"
        tdf.RefreshLink
        Set tdf = Db.TableDefs("UPP")
        tdf.Connect = "Text;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & percorso & "\input\UPP.csv"
        tdf.RefreshLink
      
        percorso = oTXT.WriteLine
        File.Close

 

[Gasman]

Well, I suppose the next thing to ask is, do they link manually?

 

[Boromyr]

Well, I suppose the next thing to ask is, do they link manually?

Yes....

 

[Gasman]

So see if Pat's utility will do it.?

Split tables

I have a split DB that I want need to copy and use in a different manner. The new use will be putting a version of the DB on indvidual laptops (sales) so they can use a price tool program. This will require copying the current FE and BE DB's to a new name - "blahblah-be - Sales" and "blahblah...

www.access-programmers.co.uk

If so, see how she does it?
TBH, these are just steps I would be taking if I had this problem.

 

[Isaac]

Can you reply to my post #5

 

[Boromyr]

I have noticed that the subfolder for the excel files is "utility" but for the cvs file it is "input". Do you have the csv's in the correct directory?

Best,
Jiri

Yeah, the project has 3 directory, "utilità" (utility in italian) that contain service files for DB, Input that contain files users will update and Output where the DB works in rest of code.

 

[Boromyr]

I was thinking exactly the same. What is that percorso variable?

"Percorso" is "Path" in italian. I don't have much fantasy

 

[Boromyr]

@Isaac

Here the image with your code of post #5
Sorry I did not see it

Before I decide to write here, I looked for a solution on web for 4 days, with no result, but I think that possibilities are 2:
1) an error in the Text property of tdf.Connect in this part "Text;HDR=YES;IMEX=2;ACCDB=YES" or ""Text;HDR=YES;FMT=TabDelimited"
Text should be correct
HDR=YES should be correct becouse the first line contain name of columns
"IMEX=2" & "ACCDB=YES" I don't know if correct. I don't found an explanation for this part
FMT=TabDelimited is another try that I made, that means that data in file is separated from Tabulation, but don't work....
2) an error with drivers used in project

In other forum I have read some information about OLEDB & ADODB, but is too complicated

 

[Gasman]

I'm not going to even change this any more than I have had to.

Run this and see what it produces for the csv files. I ask as I linked to a csv file and marked it s Tab delimited and this produced.

Text;DSN=LBG UK Recipients Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=850;ACCDB=YES;DATABASE=C:\Users\Paul\Documents

so see what it produces for yours, now you have them successfully linked. manually?

Public Sub RelinkJetTables() '( OldPathName As String, NewPathName As String)
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim TDS As DAO.TableDefs
    Dim ErrCount As Integer
    Set db = CurrentDb
    Set TDS = db.TableDefs

On Error GoTo Err_Proc
'Loop through the tables collection
    For Each td In TDS
        Debug.Print td.Connect
'        If td.Connect = ";DATABASE=" & OldPathName Then
'            td.Connect = ";DATABASE=" & NewPathName 'Set the new source
'            td.RefreshLink 'Refresh the link
'        End If
    Next 'Goto next table
    If ErrCount = 0 Then
        MsgBox "Relink complete", vbOKOnly + vbInformation
    Else
        MsgBox "Not all tables were relinked. " & ErrCount & " failed.", vbOKOnly
    End If
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case 3011
            ErrCount = ErrCount + 1
            Resume Next
        Case Else
            MsgBox Err.Number & "-" & Err.Description
            Resume Exit_Proc
    End Select
End Sub

 

[Isaac]

@Isaac

Here the image with your code of post #5

Alright, thanks for posting back the result. This answers at least one question, the question of, "is the path correct". If the path was not correct, that message box would have returned a blank (nothing).
I just linked a CSV file in an Access database. The CSV file has no spaces in the name (CSVNoSpaces.csv), and neither does the path. The resulting tabledef's connect property was:

Text;DSN=CSVNoSpaces Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=65001;ACCDB=YES;DATABASE=C:\Users\MyUserName\Desktop

Then I created the same (tab delimited CSV file), except with spaces in the name (CSV With Spaces.csv), and checked out the tabledef's connect property, which was:

Text;DSN=CSV With Spaces Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=65001;ACCDB=YES;DATABASE=C:\Users\MyUserName\Desktop

From this exercise, I will call a couple of things out that this indicates to me:
1) The spaces in the csv file name did not cause any problems, and required no special handling in the connect string
2) Oddly, the bit of the connect string which mentions the HDR was HDR=NO (even though I linked it using the GUI, WITH headers, AND, it shows up properly for me in Access table datasheet view - with headers.

Perhaps you can glean something from that.

Here is a totally different thought.
To refresh this link: Instead of manipulating the tabledef's connect string, why not just write a line of code that re-links the csv file from scratch? There is a lot more accumulated wisdom and code sampling on this topic, compared to the topic as the approach you're trying. Therefore, if you're struggling from your approach, I would recommend the other approach instead. Quite possibly a quick fix...Delete the Tabledef in question, and simply re-link it. DoCmd.TransferText acLinkDelim ....
If you do end up going this route, create a Specification first.

 

[Boromyr]

Alright, thanks for posting back the result. This answers at least one question, the question of, "is the path correct". If the path was not correct, that message box would have returned a blank (nothing).
I just linked a CSV file in an Access database. The CSV file has no spaces in the name (CSVNoSpaces.csv), and neither does the path. The resulting tabledef's connect property was:

Text;DSN=CSVNoSpaces Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=65001;ACCDB=YES;DATABASE=C:\Users\MyUserName\Desktop

Then I created the same (tab delimited CSV file), except with spaces in the name (CSV With Spaces.csv), and checked out the tabledef's connect property, which was:

Text;DSN=CSV With Spaces Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=65001;ACCDB=YES;DATABASE=C:\Users\MyUserName\Desktop

From this exercise, I will call a couple of things out that this indicates to me:
1) The spaces in the csv file name did not cause any problems, and required no special handling in the connect string
2) Oddly, the bit of the connect string which mentions the HDR was HDR=NO (even though I linked it using the GUI, WITH headers, AND, it shows up properly for me in Access table datasheet view - with headers.

Perhaps you can glean something from that.

Here is a totally different thought.
To refresh this link: Instead of manipulating the tabledef's connect string, why not just write a line of code that re-links the csv file from scratch? There is a lot more accumulated wisdom and code sampling on this topic, compared to the topic as the approach you're trying. Therefore, if you're struggling from your approach, I would recommend the other approach instead. Quite possibly a quick fix...Delete the Tabledef in question, and simply re-link it. DoCmd.TransferText acLinkDelim ....
If you do end up going this route, create a Specification first.

I try to make the same with the same file in old version ofcDB:

Print DBEngine(0)(0).TableDefs("Fatture_Scadute").Connect
Text;DSN=Fatture_Scadute - specifica di collegamento;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=850;ACCDB=YES;DATABASE=C:\Nuovo QC_DT\Input

With

tdf.Connect = "Text;DSN=Fatture_Scadute - specifica di collegamento;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=850;ACCDB=YES;DATABASE=" & percorso & "\input"

it works!!!

So the problem was the filename in path, that work for .xlsx, I suppose, for the name of sheet....nice!

Thanks you all for help