Excel query an Access select query to a secured Access DB -


i apologize if title bit confusing, let me explain. part of work processing / tagging large datasets erp system , make more meaningful reporting purposes. use 2010 access databases manipulate , tag data regularly create static datasets user can point excel pivot tables to, allows them create reports clients.

we have had many issues of users using default excel connection [share deny write] oledb connection. locks "source" .accdb, preventing dropping updated source.accdb file if 1 of dozens of users has excel report open after refreshing file.

to around this, found creating "shadow" .accdb contains linked tables or queries query source.accdb, these connections not lock source.accdb allowing overwrite file updated data please.

some users still seek out source.accdb , link directly it, trying password protect them prevent this. have no issues creating select shadow.accdb query source.accdb using proper password. problem arises when try link of queries in shadow.accdb excel, thrown "invalid password" error.

the issue seems excel oledb (or odbc) connection not being passed password sql query inside shadow.accdb?

yes, understand backasswards way of setting data sources, there many users police connections, have no option of changing programs use, , there no way database end kill excel connection access db, , have work with.

while doubt has specific experience setup, ideas great. example of connections shadow.accdb source.accdb below:

select * table in '' [ms access;pwd=password;database=c:\directory\source.accdb];

try use mode = share deny none instead of share deny write, should allow open , modify access file while users connected excel files, must update data last ones added while connected db.
can specifyng property share deny none while creating connection in tab advanced or can edit existing connection opening properties, tab definition, , edit connection string replacing mode = share deny write mode = share deny none.


Comments

Post a Comment

Popular posts from this blog

java - Spring Data JPA: Why findOne(id) executing delete query internally? -

i have 1 application using @manytomany annotation link 2 different entities 1 parent entity. not sure if have implemented properly. when execute .findone() on parent entity's repository find it, @ first fetch data along executing delete queries internally delete record mapping table (a table jpa creates after adding @manytomany annotation.). following generated sql queries , code snippet: java class @entity @table(name = "inventory") public class inventory { @id long id; // many other properties @manytomany(fetch=fetchtype.lazy) private list<stream> streams; @manytomany(fetch=fetchtype.lazy) private list<subject> subjects; } there no other annotation i've put in stream , subject entity class link inventory class. when run app, create following tables: inventory stream subject inventory_stream inventory_subject (inventory_stream , inventory_subject, 2 tables created automatically, don't k...
Read more

python - Mongodb How to add addtional information when aggregating? -

i beginner , wrote line pipeline works, wanna add other information output, screen name , or number of tweets.i tried add under $group gave me syntax error everytime here pipeline: def make_pipeline(): # complete aggregation pipeline pipeline = [ { '$match': { "user.statuses_count": {"$gt":99 }, "user.time_zone": "brasilia" } }, { "$group": { "_id": "$user.id", "followers": { "$max": "$user.followers_count" } } }, { "$sort": { "followers": -1 } }, { "$limit" : 1 } ]; i using on example : { "_id" : objectid("5304e2e3cc9e684aa98bef97"), "text" : "first week of school on :p", "in_reply_to_status_id" : null, "retweet_count" : null, ...
Read more

java - Incorrect order of records in M-M relationship in hibernate -

Image
i have 4 records in bridge table in mysql database. using @manytomany in hibernate, accurate order of employees incorrect incorrect of projects. following required material figure out doing mistake. this source. @entity @table(name="project") public class projectbean { @id @generatedvalue @column(name="project_id") private int projectid; @column(name="title") private string projecttitle; @manytomany(mappedby="projects") private collection<employeebean> employees; public projectbean() { employees = new arraylist<employeebean>(); } //getters & setters } @entity @table(name="employee") public class employeebean { @id @generatedvalue @column(name="employee_id") private int employee_id; @column(name="first_name") private string ...
Read more