Linked Table in MS Access via PowerShell

Question

How can I access a linked table in Access when using PowerShell?

I have a .mdb database, within which is a linked table to an external CSV file. When using Access, all works well - I can open the table with no problems, and perform SQL queries on the contents.

However, I am now trying to automate the initial data-load using PowerShell (since VBScript is now deprecated), using the following method:

$adOpenStatic = 3
$adLockOptimistic = 3

$objConnection = New-Object -comobject ADODB.Connection
$objRecordset = New-Object -comobject ADODB.Recordset

$objConnection.Open("
    Provider =  Microsoft.ACE.OLEDB.12.0;
    Data Source = C:\Data\My_Frontend.mdb
")

$objRecordset.Open(
    "Select * from Linked_Table",
    $objConnection,$adOpenStatic,$adLockOptimistic)

$objRecordset.MoveFirst()

do {
    $objRecordset.Fields.Item("product").Value;
    $objRecordset.MoveNext()
} until ($objRecordset.EOF -eq $True)

$objRecordset.Close()
$objConnection.Close()

If I query an ordinary table from within PowerShell, everything works fine. If, however, I try and pull data from the linked table, I get the error:

The text file specification 'Patient-orders Link Specification' does not exist. You cannot import, export, or link using the specification.

What is confusing me is that I do have a link specification saved, and being used to link/import the data. Therefore:

Why am I getting this error;
more importantly, how do I fix this?

Thank you!

What is the location of the linked CSV file? You say I do have a link specification saved. Does that contain a relative path to what is the current path in Access or do you use an absolute path for that? (PowerShell will probably use a different current working directory) — Theo
– Theo, Commented Jun 9 at 15:10
If you have some working vbscript can you post that for comparison. — mclayton
– mclayton, Commented Jun 9 at 15:28
@Theo -- I use absolute paths for tables. Have been bitten before. I have no idea how/where Access stores the link specification file. — Phil
– Phil, Commented Jun 9 at 15:44
@mclayton -- I was going to go down the VB route, but then decided that since it was officially deprecated, I'd learn to use PS instead. Therefore no other solution tried. — Phil
– Phil, Commented Jun 9 at 15:44
SOLVED! Thank you for being patient. I will delete this question shortly -- It was my error.... — Phil
– Phil, Commented Jun 9 at 15:55

Phil · Accepted Answer · 2025-06-09 16:10:10Z

2

SOLVED.

Error on my part. Not only am I using ANSI 92, but I'm also restricting access to every table. and preventing visibility of the underlying database structure.

It appears that Microsoft.ACE.OLEDB.12.0 requires visibility...

answered Jun 9 at 16:10

Phil

436 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Parfait Jun 14 at 17:01

If you only need to query the CSV data, you can do so directly without the .mdb file or linked table using same Microsoft.ACE.OLEDB.12.0.

Collectives™ on Stack Overflow

Linked Table in MS Access via PowerShell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related