I needed to use this one table in excel for 5 different databases, is there
an easy way to do this instead of making 5 different copies of the file and
updating all of it everyday? The Excel file is downloaded everyday from a
website so I cant import it.
Im sorry my first question was not complete. I already linked the Excel file
to these 5 access databases, the problem is that the Excel file cannot be
used by more than 1 user at the same time, even if it is in shared mode. An
error message like : "Cannot find the file...The Microsoft Jet Rocket
supersonic turbo engine is not working"(sumthin like that : )).
sunberries said:No need to be upset Mr. MVP Sir, you can pass if you dont feel like
its worth your time.
Like I mentioned, the issue and objective is to
have one lone Excel file serve multiple databases at the same time.
One file to rule them all. Linking of course is the obvious answer,
but you see.. it DOESNT WORK! .
All databases will link to the excel
file, but once all 5 databases start using this linked table an error
message pops up basically because the file becomes exclusive to the
first database that opened it. As I mentioned again, the xls file is
already in shared mode. FYI, one cannot CTRL-C an error message, it
just wont do it.
Have a great Turbo-jet charged day!!
Dirk Goldgar said:Linking can work, depending on your requirements. If you don't need
to be able to update the Excel file, you can do it just fine.
onedaywhen said:I tried the following query in five mdb's open in five instances of
Access2000:
SELECT MyKeyCol
FROM [Excel 8.0;HDR=YES;Database=C:\db.xls;].[Sheet1$];
The Excel workbook was only unavailable while one mdb had the Query
object 'open'; presumably this has the effect of opening the Excel
file in exclusive mode. When the Query object was closed again the
Excel file was once again available to the next mdb.
Is this your experience?
I'm still waiting for John V's 'refund' for the advice he gave me the
other day about the non-support for CHECK constraints in the 'Access'
SQL syntax:
http://tinyurl.com/5mzyt
Looks like he made a mistake. <shrug> I'm sure the check is in the
mail.
onedaywhen said:go!
Could you make that out to Sterling, please <g>. Although the pun
"cheque's in the post" doesn't translate as well.
go!
Could you make that out to Sterling, please <g>. Although the pun
"cheque's in the post" doesn't translate as well.
onedaywhen said:Rather, the five applications should only be connected to the workbook
long enough to fetch the records e.g. the equivalent of this:
Sub jmctest()
Dim Con As ADODB.Connection
Dim rs(4) As ADODB.Recordset
Dim lngCounter As Long
Set Con = New ADODB.Connection
With Con
.CursorLocation = adUseClient ' client-side cursor
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';" & _
"Data Source=C:\Tempo\db.xls"
.Open
' Create five disconnected recordsets
For lngCounter = 0 To 4
Set rs(lngCounter) = .Execute( _
"SELECT key_col, data_col FROM [Sheet1$]")
rs(lngCounter).ActiveConnection = Nothing ' <<< disconnect
Next
.Close
End With
' Use recordsets e.g.
For lngCounter = 0 To 4
Debug.Print rs(lngCounter).GetString
Next
End Sub