VBA: Create Linked Tables

[Marcster]

Hi People,

Does anyone know if it's possible to create a Linked Table to a .csv file and a SQL Server linked table in VBA without using a DSN file?.

What I'm after is, if the above is possible, is for the user to be able to select a csv file from an Open File dialog to obtain the csv file path.
A userform will handle the SQL Server database name and table.
Which will create linked tables from where a Query on those tables will be carried out. Any previous linked tables to be deleted.

Thanks,

[CreganTur]

Here's a procedure for creating a linked table with an Excel spreadsheet:

'Link table declarations
Dim myDB As DAO.Database, tbl As TableDef
Dim stConnect As String
Dim LinkExcel As Variant

'make the selected Excel file into a Linked Table
Set myDB = CurrentDb()
'connection string to make spreadsheet into linked table
stConnect = "Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & strFilepath
Set tbl = myDB.CreateTableDef("mySheet")

tbl.Connect = stConnect '<<<Open connection between database and spreadsheet
tbl.SourceTableName = szSheetName '<<<Connect to specified worksheet
myDB.TableDefs.Append tbl '<<<Add mySheet to database TableDefs
LinkExcel = True '<<<Creates linked table

Just replace strFilePath with the filepath to your desired Excel file. You'll also need to Dim that variable.

You can do something similar using the correct SQL connection string.

[Marcster]

Hi Randy,

I need the linked table to be linked to a .csv file not an Excel file.
So unsure what the line stConnect should be?...

Thanks,

[CreganTur]

You can find the connection string you need here (http://www.connectionstrings.com/).

[Marcster]

Cheers, i'll give it a whirl...