I have access to a remote database that consists of over 100+ csv files with a dozen columns and over 150k lines in each file; about 16g of total data. I need to create an Access external link to each file for pulling pivot data to be exported for each individual file. I can do this manually with Access>External Data>New Data Source>From File>Text File> file address” and then create a query for each linked file. When I use a PS script it fails.

My intent is to create these Access links with Powershell.

The following is an example of by script;

$filePath = "$env:userprofile\Desktop\Donation_Data\itcont_2020_20191129_20191224.csv"

$accessApp = New-Object -ComObject Access.Application

$accessApp.Visible = "True"   #I want to see Access opened to make sure the link is installed

$databasePath = "$env:userprofile\Desktop\Donations.accdb"

$accessApp.OpenCurrentDatabase($databasePath)

$db = $accessApp.CurrentDb()

$tableDef = $db.CreateTableDef("itcont_2020_20191129_20191224")

$tableDef.SourceTableName = "itcont_2020_20191129_20191224.csv"

$tableDef.Connect = "Text;DSN=itcont_2020_20191129_20191224 Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;ACCDB=YES;DATABASE=C:\Users\fkram\Desktop\Donation_Data"

$db.TableDefs.Append($tableDef)

$tableDef   #Shows the tableDef configuration is in memory ready to be appended.

The problem shows after implementing $db.TableDefs.Append($tableDef). You received the error OperationStopped: The text file specification ' itcont_2020_20191129_20191224 Link Specification' does not exist. You cannot import, export, or link using the specification.

Here is the thing. I can manually link the same csv file using Access and following Access>External Data>New Data Source>From File>Text File>” file address. When I then pull the $db.TableDefs command in powershell it shows the exact same configuration as $tableDef configuration I tried to append.

Now the interesting aspect. If I delete the manually installed link, then re implement the powershell $db.TableDefs.Append($tableDef) command it works. It seems there is something about using the above powershell script that has a missing step that interferes with the link being installed correctly.

Any ideas?