I have a database with many (linked to sql) tables and a couple queries. The people that want the information in these queries want them in excel. So I have linked excel files to the data source (access queries) for them to view, they love it becuase of the magic (lol) that it performs when they open the spreadsheets (refreshing on open).
Here is my problem. When they open their spreadsheet, i cant make any changes to the database because i assume that the spreadsheet is keeping an open link to the query, causing me to get the "This database has been opened read-only..." error when they open.
Is there any way around this? The data on the spreadsheet is only refreshed on open...shouldnt it only query the access database on open, and not (for a lack of a better word) "hold" the database query for itself?
Any help is appreciated, even if it is "Sorry, thats the way it works". Thanks.
When you create the connection in Excel to Access, in the properties of the connection, there is a connection string. In the connection string, there is an attribute called mode. That needs to be set to Read.
8  5409 
This might not be the best way, but you might try storing the tables in Excel and linking the database to the Excel files. If I'm understanding your question correctly, it sound like you have it the other way around. I've never tested this, but it might be worth a try.
The actual tables are linked to the SQL tables that our CRM enterprise database controls. The nice thing about reading from the queries is that they get current data, without having to go in and export the data first.
Oh, okay. I missed that you were use an SQL database and not MS Access.
Ok, i will explain. We use a database at work. It is a sql database. I have created an access database with linked tables, linked to all of the sql tables. Then i create queries (in access) pulling infomation from the linked tables, and then link to the access queries by using a data connection in excel. I dont really like writing queries outside of access, because i am not very good with SQL, and have found the very easy option of writing them in access, and then pulling the data through excel. It works very well, but something is making that database open in read only.
In the connection string, set the mode to Read.
Okay, I understand now. Hum... I've never dealt witha situation like this so I don't know. Sorry.
When you create the connection in Excel to Access, in the properties of the connection, there is a connection string. In the connection string, there is an attribute called mode. That needs to be set to Read.
Rabbit, thanks for your second post....becuase i was a bit confused with the first. Thanks. For now i will say it sounds promising, now i just need to get all of there spreadsheets changed, lol.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ivaylo Georgiev |
last post by:
In my app I have to open many doc files that after users edition have to be updated in database /MS Access/
I use System.Diagnostics.Process.Start() method
When the file is opened with WINWORD if...
|
by: Alex Sidorov |
last post by:
for example, one application is writing some binary data to a file, and I
need to read the data at the same time. I tried to use FileGet and
BinaryReader, but they all say "End Of File" when I call...
|
by: bwhite |
last post by:
I have an access database application that is used to calculate landed
costs for foreign goods imported into vartious countries. I am trying
to determine an approach so that a user will be...
|
by: Randy |
last post by:
Hi,all
I met one problem of connecting Access database in asp.net web program.
I use ODBC link to Access, the connection string like
"PWD=12345;DSN=QADLinkMDB;DriverId=25;MaxBufferSize=2048;FIL=MS...
|
by: wingleader |
last post by:
Hi!
I'm looking for ways of uploading image file to mysql database. I know, that it should be possible, but don't know how to do it.... could someone help me?
|
by: vanderschoork |
last post by:
Hi,
I'd like to record how many times an Access database I've created is opened.
Ideally I'd like to record the date\time the user opens it and their user name into a separate table in the...
|
by: bobh |
last post by:
Hi All,
Anyone have code to share that would detect if a database was opened
to database window view??? and since holding the 'you know which key'
down, no form is loaded, the autoexec marco...
|
by: stonward |
last post by:
Hi Folks,
This one is from work!
Running Access 07, usually our default saving format is 2003 - not our fault.
I want to publish a database to our SharePoint system, but the database must be...
|
by: Ryno Bower |
last post by:
Hey guys. I'm writing a program and want to do the following.
I have a form for the company details called "companydetails".
So when I open the database I want the form to display first if the...
|
by: titli |
last post by:
I have created the excel worksheet from MS Access using the following code:
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
With xlApp
...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
