Linking 1 excel file table to multiple databases

[Guest]

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.

 

[John Vinson]

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.

You should be able to use File... Get External Data... Link to have
all five databases link to the table, rather than importing it.

John W. Vinson[MVP]

 

[Guest]

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 : )).

 

[John Vinson]

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 : )).

Well, it's a bit easier if you post the actual problem rather than
some other problem; and it's easier yet if you select the error
message balloon, type Ctrl-C to copy the error message, and then paste
it here (or retype the error message).

We cannot see your computer. We don't even know what operating system
or version of Office you're running. Give us a bit of help and we'll
try to reciprocate!

John W. Vinson[MVP]

 

[Guest]

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.

Here`s the cast:
A lone but powerful Excel file
5 Access databases
Each one of those databases having multiple users.
An error message saying that the file cannot be opened bec its on exclusive
use.blah blah blah
A helluva lot of Dell PCs running on XP, Access 2002,
An access expert con artist and a bunch of admins.

Have a great Turbo-jet charged day!!

 

[Guest]

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.

Here`s the cast:
A lone but powerful Excel file
5 Access databases
Each one of those databases having multiple users.
An error message saying that the file cannot be opened bec its on exclusive
use.blah blah blah
A helluva lot of Dell PCs running on XP, Access 2002,
An access expert con artist and a bunch of admins.

Have a great Turbo-jet charged day!!

 

[Dirk Goldgar]

No need to be upset Mr. MVP Sir, you can pass if you dont feel like
its worth your time.

I'm sure John will be happy to refund every penny you paid for his
advice. Are you always this obnoxious to people who are trying to help
you?

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! .

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.

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.

FYI, one often can CTRL-C an error message. I've never quite figured
out why you sometimes can and sometimes can't.

Have a great Turbo-jet charged day!!

My day's pretty good, thanks. How's yours?

 

[Dirk Goldgar]

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.

Actually, I have to temproarily retract this. I had tested a method
that seemed to work, but now it isn't working. So I have to go back and
try again.

 

[onedaywhen]

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 sure John will be happy to refund every penny you paid for his
advice.

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

Jamie

--

 

[Dirk Goldgar]

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?

That's what I'm finding. It looks to me like the Jet IISAM for Excel
places a lock on the file. I've tried various things to get around
that, with no success -- though I'd thought my first attempt, worked, it
seems I was simply mistaken. I'm wondering if the ODBC driver for Excel
might work.

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.

 

[John Vinson]

Looks like he made a mistake. <shrug> I'm sure the check is in the
mail.

<chuckle> Yep. Apologies for the misstatement plus $0.00 - here ya go!

John W. Vinson[MVP]

 

[onedaywhen]

<chuckle> Yep. Apologies for the misstatement plus $0.00 - here ya

go!

Could you make that out to Sterling, please <g>. Although the pun
"cheque's in the post" doesn't translate as well.

Jamie.

--

 

[Douglas J. Steele]

go!

Could you make that out to Sterling, please <g>. Although the pun
"cheque's in the post" doesn't translate as well.

Ooohh. I really wish you hadn't mentioned "pun" to John... <g>

 

[John Vinson]

go!

Could you make that out to Sterling, please <g>. Although the pun
"cheque's in the post" doesn't translate as well.

Ok... £0.00 it is!

And I'll pass on the opportunity to tell the one about the Czech being
in the male (bear)...

John W. Vinson[MVP]

 

[onedaywhen]

I'll pass on the opportunity to tell the one about the Czech being
in the male (bear)...

Ouch! Now it's me who should be apologizing to *everyone* <vbg>.

Jamie.

--

 

[onedaywhen]

That's what I'm finding. It looks to me like the Jet IISAM for Excel
places a lock on the file. I've tried various things to get around
that, with no success

I suppose as an administrator I may have the Query object open
exclusively but this should not be happen in an application.

I assume the OP only wants read access to the Excel workbook. Updates
to an Excel source via a linked table is not recommended. I think such
a link is opened in 'import mode', equivalent to IMEX=1 in the
connection string, to ensure the registry settings are honored and the
data types are seen as expected (for more details see
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/).
Updating in 'import mode' is not recommended due to 'unpredictable
results'. I've also read in these ngs that such updates can cause
corruption in the mdb.

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


Jamie.

--

 

[Guest]

Thank you for your solution, i think it should do the trick.
If its ok to ask, where do I put this code?

Finally, what if I use a macro "transferspreadsheet" instead, I know its
gonna append the table everytime the macro is ran, but is there a way to
overwrite existing data on a table instead of adding data into it during
"transferspreadsheet"?
Thanks again.

 

[onedaywhen]

Thank you for your solution, i think it should do the trick.
If its ok to ask, where do I put this code?

My code is a demo e.g. it uses a five element array to simulate five
separate applications. I can't advise on how to use disconnected ADO
recordsets with MS Access Forms because I've never used MS Access Forms
in 'real life'. I suggest you repost.

Jamie.

--

 

[Dirk Goldgar]

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

I've tried a variety of things, and can't get a lasting, non-exclusive
link to the spreadsheet. I won't say that I;'ve given up, but my time
to work on the problem is limited. If read-only access to the
spreadsheet is all that's required, it seems to me that the easiest
thing to do is just import the data into a local table.
TransferSpreadsheet could be used for that, executed either on a daily
basis, or whenever the database is opened, or by checking the workbook's
last-modified date to see if it's been changed since the last time the
table was imported. With a local table containing the data, a lot of
things become easy.