Barry Rainbird
New Member
- Joined
- Feb 7, 2013
- Messages
- 14
Hi Guys
I have recently converted an Access 2007 database from MDB to ACCDB and all is fine, it works well. However, I also have a largish Excel.xlsx spreadsheet which draws down information from my Access 2007 database via many links. Try as I might I cannot find how to replicate the type of linkage to accommodate ACCDB where they were all originally set up to accommodate MDB. Each link is part of a table in Access. Now when I setup a link toi the ACCDB table all I seem to be able to do is draw down the whole table's data-set and not part as used to be the case. Normally of course this wouldn't matter as I could ignore all the information I dont need. But in this case I have a hidden sheet in Excel which does the calculations and hundreds of references to these existing linkages. This has worked fine for several years and to try and remake it to accommodate the different dataset setup would take many days of work.
As an example of the many connection strings:
DBQ=C:\MSOffice\Access\New Park Nursery\Management System.mdb;DefaultDir=C:\MSOffice\Access\New Park Nursery;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;ImplicitCommitSync=Yes;MaxBufferSize=512;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;
And an example of the same string's Command Text:
SELECT `Booking Database`.`Wednesday PM`, `Booking Database`.`Date of birth`, `Booking Database`.Surname, `Booking Database`.`Child Leaving Date`
FROM `C:\MSOffice\Access\New Park Nursery\Management System`.`Booking Database` `Booking Database`
WHERE (`Booking Database`.`Wednesday PM`=1)
ORDER BY `Booking Database`.`Date of birth` DESC
I have tried altering the existing details of all the connection strings an example as follows:
DBQ=C:\MSOffice\Access\New Park Nursery\Management System.accdb;DefaultDir=C:\MSOffice\Access\New Park Nursery;Driver={Microsoft Access Driver (*.mdb)(*.accdb)};DriverId=281;FIL=MS Access;ImplicitCommitSync=Yes;MaxBufferSize=512;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;
but to no avail. Can anyone suggest a way forward which doesn't require me to re-write the whole spreadsheet connection calculation sheet?
Many thanks
Barry R
I have recently converted an Access 2007 database from MDB to ACCDB and all is fine, it works well. However, I also have a largish Excel.xlsx spreadsheet which draws down information from my Access 2007 database via many links. Try as I might I cannot find how to replicate the type of linkage to accommodate ACCDB where they were all originally set up to accommodate MDB. Each link is part of a table in Access. Now when I setup a link toi the ACCDB table all I seem to be able to do is draw down the whole table's data-set and not part as used to be the case. Normally of course this wouldn't matter as I could ignore all the information I dont need. But in this case I have a hidden sheet in Excel which does the calculations and hundreds of references to these existing linkages. This has worked fine for several years and to try and remake it to accommodate the different dataset setup would take many days of work.
As an example of the many connection strings:
DBQ=C:\MSOffice\Access\New Park Nursery\Management System.mdb;DefaultDir=C:\MSOffice\Access\New Park Nursery;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;ImplicitCommitSync=Yes;MaxBufferSize=512;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;
And an example of the same string's Command Text:
SELECT `Booking Database`.`Wednesday PM`, `Booking Database`.`Date of birth`, `Booking Database`.Surname, `Booking Database`.`Child Leaving Date`
FROM `C:\MSOffice\Access\New Park Nursery\Management System`.`Booking Database` `Booking Database`
WHERE (`Booking Database`.`Wednesday PM`=1)
ORDER BY `Booking Database`.`Date of birth` DESC
I have tried altering the existing details of all the connection strings an example as follows:
DBQ=C:\MSOffice\Access\New Park Nursery\Management System.accdb;DefaultDir=C:\MSOffice\Access\New Park Nursery;Driver={Microsoft Access Driver (*.mdb)(*.accdb)};DriverId=281;FIL=MS Access;ImplicitCommitSync=Yes;MaxBufferSize=512;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;
but to no avail. Can anyone suggest a way forward which doesn't require me to re-write the whole spreadsheet connection calculation sheet?
Many thanks
Barry R
