Dynamically link CSV to datasheet form (1 Viewer)

[ozinm]

(MS Access 2010)

Hi All,
I'm trying to find some code to allow a datasheet form to link to a csv file.
I'd like the link to the data to be dynamic so it connects when the form loads as the access database will be run from a network share by multiple people at the same time.
The CSV file will be different for each user and reside on their local PCs in their profile folders.

I've been trying variations of this snippet of code I found which is called On Form Load:

Public Sub getData(path As String, fileName As String)

Dim cN As ADODB.Connection
Dim RS As ADODB.Recordset
Set cN = New ADODB.Connection
Set RS = New ADODB.Recordset
cN.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & path & ";" & _
               "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")
RS.ActiveConnection = cN
RS.Source = "select * from " & fileName & " ORDER BY ID"
RS.LockType = adLockOptimistic
RS.CursorType = adOpenStatic
RS.CursorLocation = adUseClient


Set Me.Recordset = RS
Set RS = Nothing
Set cN = Nothing


End Sub

However, I've come to the conclusion that I don't know enough about setting up and connecting ADO data sources to a form.

Has anyone got any suggestions?

Thanks

M

 

[Poppa Smurf]

This is a simpler method

Store the CSV file in a same directory location e.g. C:\Data\csv on each user's computer.

In the database link this csv file to a new table, this table will be refreshed with the each new CSV file relevant to the user. Now use this new table as the record source for your form.

 

[ozinm]

I would do except that there's too much variation.

The CSV is generated on the fly and sometimes doesn't exist yet.
It is written to to the users temp folder which can differ per user and per PC
e.g.
set temp
TEMP=C:\Users\MyUserName\AppData\Local\Temp
or
set temp
TEMP=D:\Windows\TEMP

If I pick a specific folder it's likely that some users won't have write access to the location.

One thought does occur....
can access have a link to a CSV using a string expansion.
i.e. %TEMP%\data.csv instead of C:\Users\MyUserName\AppData\Local\Temp\data.csv