Linking to an Excel File w/o locking

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Excel file that I want to set up a link to in my Access DB. The
problem is I only want to use it as a reference (never to be updated through
Access). When I Link to it, it locks it from others using it in Excel on
their workstations.

I can import it, but I wanted something that was more automated. I don't
want to be in the habit of always having to make sure I re-imported the
latest version of the Excel file.

Any input would be greatly appreciated.
 
I can import it, but I wanted something that was more automated. I
don't want to be in the habit of always having to make sure I
re-imported the latest version of the Excel file.
Click to expand...

You could always automate the import...


Tim F
 
I know how to manually import, but not sure how to convert that into a macro.
I know how to automate a macro to run, just not sure how to code it.
 
Tim said:
You could always automate the import...
Click to expand...

Querying the Excel file with a disconnected recordset does not lock the
file e.g.

Sub testtim()
Dim Con As Object
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\db.xls;" & _
"Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
.CursorLocation = 3
.Open
End With

Dim rs As Object
Set rs = Con.Execute("SELECT data_col FROM [Sheet1$];")
Set rs.ActiveConnection = Nothing ' <<< disconnect
MsgBox rs.GetString(2)
End Sub

Jamie.

--
 
Back
Top