Search multiple Access databases. Using older dbs generated by previous versions of Access

[Pete]

I am new and just about to jump in and learn Access. My background is web design and I have a very basic knowledge of mysql but no Access knowledge as yet.

I need to know in general if I can use the latest Microsoft Enterprise 365 version of Access to do the following before I committee too much time learning and would very much appreciate any assistance.

Can I use Access on line to ( Entreprise 365 current version):
1. store old versions of databases on-line up to 10 years old. All will have the same set of tables just different data .
2. Search across multiple databases, produce reports and format these into Excel or word.
3. add new database tables from an excell spreadsheet, presumably in csv format and having the same configuration as old tables.

The databases are from Exhibitions, having details of entrants, entries, results. We might search for eg all results for one entrant, email and address for entrants or produce a report of entrants with a certain level of result. These are just examples. We may want to search all databases of a limited set defined by date range.

Any help appreciated.
Pete

[Pete]

i suspect the lack of replies means my question was not well formed and it certainly showed a basic lack of understanding regarding Access. My apologies.
I have figured out that:
1. Access does not run in the Cloud. Both Access and Publisher require a Windows machine, do not run on Mac and only run in the client. Hence my idea we could run Access in a web browser was mistaken.
2. Its possible to convert databases made by older versions to the latest version fairly easily though I am not sure how far back you can go. We would have a few made in 2007 but I have yet to see them so I dont know what version of Access I am talking about

I am still not sure about the practicalities of searching multiple databases . I see the max size of a database is 2 gig including associated objects .

Can anyone tell me is there a limit to the number of databases that can be searched simultaneously ?
As an alternative, could we combine or link say 50 small databases together and search that ? The total size is going to be nowhere near 2 gig as individually they are small.

Any suggestions welcomed!

Pete

[ssanfu]

Its possible to convert databases made by older versions to the latest version fairly easily though I am not sure how far back you can go

I have converted Access 97 dBs to Access 2000, then to Access 2010. You could probably (but I have not tried) convert A2000 to A2016.

I am still not sure about the practicalities of searching multiple databases .

Me either. One thought is to use code to open each BE (the other databases) and search, writing the matching records to a temp table (the records are temp, not the table).

Another option since you say the tables/field names are identical in all of the 50 small databases. You could have code to search the first linked dB, (writing the matching records to a temp table or Excel or a CSV text file) then relink to the next dB, and so on until the last dB is searched.
I've had two back ends, a Dev and a Prod, that I would click on a button in the FE and change the table links from one to the other. Should be able to do that for multiple BEs (even 50).

[Pete]

Steve,
Thanks for taking the time to reply. I find it difficult to know what the right questions are when you are starting out.
Good to know how you managed 97>97>2000>2010. I doubt we need to go back any further.

I saw another similar suggestion about coding to open respective databases so it seems this is one option. I assume we can write a loop to use a date range to terminate the searches.
Now that I have general approaches I can start to learn how to use Access with a rough idea of what I may need to do.
Thanks again.

Pete

[CJ_London]

your biggest issue is old databases. Their are two access db formats. The old .mdb which was used from access inception to access 2003. and .accdb from access 2007.

Assuming you are talking about data only, access 365 (there is no online version of access, it is still client based - but since 2007 can also link to back ends stored on the web such a sharepoint lists or sql azure) will read both formats, but: both types have different versions over the years and from access 2013, only .mdbs created in access 2000 or later can be read. It won't be long before 2000 and eventually 2003 will also cease to be readable by the latest access versions.

So you need to be clear about what you mean by old versions. A database may have been created last year, but if it was created in a 97 version of access, it will not be readable by access 365.

with regards your other two questions, they are wide ranging but

yes you can search multiple databases
yes you can add new tables

how you do it is a different question and depends on your specific situation. It may be you would create one db and copy all the data across into a common format (probably the best long term solution). You might use linked tables, you might use vba code, you might use union queries with inline dsn.

Access is an application in two parts. A backend (JET or ACE) of tables only (and could be something else, sql server, MySQL etc) and front end application creator (forms/reports/etc), a bit like visual studio or other wysiwyg developer environments. Consequently users should not share a created front end application - as with any other product each user has their own copy of the front end. With your web background, you should understand this. Data is in a backend somewhere, each user has a browser application which loads (their own copy of) web pages - they don't share a browser.

Adding tables from excel can be done, but you need to know what you are doing. Excel is about as far away from a normalised data structure you can get - great for analysing, not so good at data management.

Finally, where your store all these databases matters. A .mdb/.accdb is an interactive file that allows multiple users to open and modify it at the same time. Onedrive and similar data storage offers allows you to share data, but does not allow interactive files to be modified by multiple users at the same time. What happens is what happens with word, excel etc (which are not interactive). With these, the first user to open the file locks it and retains a copy in memory to work on. When finished, the file is saved and the lock removed. In the meantime any other user trying to open the file will not be able to save it, except under a different file name. Even just opening a .mdb or .accdb file changes it as it monitors user activity.

[NoellaG]

Hi,

if you know MySQL, why not import all old Access data to MySQL and use an ODBC link to connect the Access front end to MySQL. Or download a free version of SQL express and upload all data from all old access databases to this database. This way you have 1 place to store and retrieve the data.

Kind regards
NG

[orange]

Pete,
What do you do today when you get a request requiring a search? That may give us some idea of the frequency, response time etc. How often, and with what consequences, do you need details of XXX from 2006? etc...

As others have said, since the data is in similar format, then a concerted effort to get them into a common(current) Access format would seem a good line of attack. Moving to SQLExpress or some other DBMS that is ODBC accessible and can handle the volumes you anticipate seems viable.
I think some samples of what sort of search is expected/occurring would help readers to focus responses.
You say your background is web design but you didn't mention database in terms of this. How many people need access to such data?
There are many parameters to consider before selecting HW/SW.

[Pete]

Ajax,
many thanks for your reply. The information you have provided seems extremely useful and should enable me to focus much more clearly on what I need to do. I appreciate the detailed information - you have clarified many of the issues I was concerned about.
Pete

[Pete]

Thanks NG.
I have to admit I have avoided Microsoft Web technology until now based on problems with open source software on Microsoft servers at times. So ODBC is new to me.
I will definitely have a look at your suggestion thouhg as on the surface it looks promising. Many thanks.

Pete

[Pete]

Pete,
What do you do today when you get a request requiring a search? That may give us some idea of the frequency, response time etc. How often, and with what consequences, do you need details of XXX from 2006? etc...

As others have said, since the data is in similar format, then a concerted effort to get them into a common(current) Access format would seem a good line of attack. Moving to SQLExpress or some other DBMS that is ODBC accessible and can handle the volumes you anticipate seems viable.
I think some samples of what sort of search is expected/occurring would help readers to focus responses.
You say your background is web design but you didn't mention database in terms of this. How many people need access to such data?
There are many parameters to consider before selecting HW/SW.

My problem is I am about to take over this set of databases and am about to learn Access so I am quite ignorant except I have some limited previous experience with mysql/php.

This is a small-scale project. We have historical databases going back to the 1990s but will restrict queries to the last 10 years. 3 - 4 databases per year with less than 100 records each.

I would be receiving queries from say less than 10 - 12 individuals over 12 months looking, for example, for a report on results for one entrant going back over 5 or 6 years. Its small scale. Other queries would be for a list of all Exhibitors with email address, categories of entries and a few other classes of information. Another would be a list of exhibitors x one specific category ( there are about 8 categories of entries).

Timeframe for reply is not critical - a day or two.
We can manage the database management with one user(me) who will run queries on behalf of others though if we had a web-based application we would make queries available available to 2 or 3 other users. Input and any editing of data is restricted to one user.
As I say, its small scale ( and hence I think access is a good way to go).

Pete