Question ms access version combinations

[marianne]

hi,

can I use version 2007 as backend and 2003 as frontend? thanks.

 

[Banana]

Theoretically, yes. Practically, there may be some potential hurdles due to the differences. Though 2007 is claimed to be 100% backward compatible, one has to take extra caution to not use 2007-only features that 2003 cannot handle.

But why even bother since 2007 runtime is free to distribute so if you already bought a license to 2007, then you wouldn't even need 2003 at all, and you can install 2007 runtime along 2003 full version so if they needed 2003 for their other databases, they can have it.

 

[marianne]

1. I just want to use 2007 database to make tables for data storage.
2. I want to use 2003 IDE as front end
3. I dont want to use other 2007 features aside from having it as data storage.
4. I want to use 2007 database as storage so that it has better encryption. I can utilize the 2007 encryption there while using 2003 as frontend.

Do you have comments on that Banana?

 

[marianne]

anyone please.....how about from atomic shrimp!

 

[Atomic Shrimp]

Sorry - I don't have 2003 in order to be able to test this - will 2003 open an accdb format file?

Is this part of the same raft of issues you've raised in other recent threads about back end db solutions?
I'm not complaining about the questions - just wondering - I'm of the general opinion that if you want a really robust and hard working back end, a database server of some kind (commercial or open source) might be better than a file-based (i.e. Access) back end.

 

[marianne]

as per research on the internet and upon experimenting, I found out that I can connect the frontend 2003 to version 2007 tables using ADO thru VBA.

1. I cannot use DAO
2. I cannot use link tables using dao or ado
3. I cannot use link tables using odbc

Therefore, I think I would rather study sql server to make tables than use 2007 to make tables as my backend db.

 

[Banana]

Is that for .mdb linking to .accdb? I would imagine that linking 2003 .mdb to 2007 .mdb should work fine (but I think that would defeat your goal of using 2007 encryption which can be only had on .accdb files)


Nonetheless, glad to see you did some research & experimenting. We all learn much more when we try.

 

[marianne]

yes that is linking frontend 2003 and backend 2007.

I was thinking of the possibility of using 2007 as backend because of its database encryption. that way, I can set aside my other posts on "security....is there another way".

As I have been experimenting on it, though we can bind the recordset of the forms using ado to backend 2007 but I only tried using one backend database. I havent tried using several backend.

By the way banana, what is your thought that you said "I would imagine that linking 2003 .mdb to 2007 .mdb should work fine".

How did you imagine it would work?

 

[Banana]

2007 is capable of working with *both* .accdb (e.g the new format) and .mdb file, and you even can create new .mdb files in 2007. While I've not actually done it, I'm sure that if you were to use a .mdb file in 2007, it can be linked like any other Access table from 2003's .mdb files.

That is why it's important to make a distinction on which files we are using, not just version because 2007 can work with either equally well. But as I said, even using .mdb in 2007 doesn't mean we can use 2007-only features and I would bet encryption is one of them.

 

[marianne]

uhmmmm....i see.

I will experiment on it again.

And Banana, do you know that there is a new application now in the internet that is able to crack 2007 database password encryption. I just got it yesterday and it disappoints me. Somehow I am now considering to SQL express.

 

[Banana]

No, but I can't honestly say I'm surprised. As explained before, it's pretty darned hard to protect a file when someone has full read/write privileges. It's one thing to lock your valuable in a safe in a locked house, but entirely another to put it in a safe and leave it out by the sidewalk in which case it's just a matter of someone picking up the safe and taking it to a place where they can crack the safe at their leisure.

Also, to be sure... does that even work on Wayne's customized encyrption?

Nonetheless, good luck with the migration.

 

[Atomic Shrimp]

Somehow I am now considering to SQL express.

Are you likely to approach the database size limit (4GB, I think)? - it's not a massive technical problem because there is an upgrade path to the full version with unlimited db size - but that introduces a sudden large cost.

Not that I'm suggesting MS licensed products aren't worth the money, but if it were me, and I was starting with a reasonably blank canvas, I'd be considering one of the open source database solutions (in fact that is what I'm considering).
If I have to learn something, my inclination is to learn something I can scale up without paying out a lot.

However, SQL Server skills are more marketable in some contexts than, say, MySQL skills, so it's never a completely cut and dried decision.

 

[Atomic Shrimp]

I just did a little bit of testing....

Using 2007, I created an .accdb (2007) back end, then I created a .mdb (2002-2003) database and tried linking some of the 2007 back end stuff in.

I got an advice dialog saying that certain data types were not supported and would not be linked, but it did let me set it up...

Now of course, all of that work was done in 2007, which knows about 2007 .accdb databases - however, it may be that even 2003 will recognise a 2007 accdb as a database and support the linked files (albeit with the aforementioned restrictions), but I can't see how it could work with an encrypted 2007 back end.

Because Access is all file-based, when you open a database application, both the front and back ends are opened/run/handled by whatever version of Access you use to open the front end - so you're only going to get the feature set from that version - and if the back end contains critical features not supported by your version of Access, it's not going to work.

 

[marianne]

To Atomic Shrimp on Post No. 12

what do you mean 4 gig limit? 4 gig limit in sql server express?


To Atomic Shrimp on Post No. 13

1. What are the data types that are not supported? Were you able to know that?
2. So when you linked the tables from 2007 to 2003, it really did appear as linked table in 2003 database?

3. with regards to encryption, my theory is that, frontend and backend are two different files. both have different features. so therefore, they retain their individual features but do not share each others features.

 

[Atomic Shrimp]

To Atomic Shrimp on Post No. 12

what do you mean 4 gig limit? 4 gig limit in sql server express?

[url="http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx]Yup[/url]

To Atomic Shrimp on Post No. 13

1. What are the data types that are not supported? Were you able to know that?

It says "attachment fields are not supported in MDB or ADP database formats. If attachment fields are included in the source database tables, those fields will not be imported - do you want to continue?"

2. So when you linked the tables from 2007 to 2003, it really did appear as linked table in 2003 database?

Yes, but it's important to note that although I did this in a 2003 format db, I was working in Access 2007.

3. with regards to encryption, my theory is that, frontend and backend are two different files. both have different features. so therefore, they retain their individual features but do not share each others features.

I think this theory will prove incorrect - or rather, the individual files may retain their features, but a feature exclusive to Access 2007 will not work when you try to run it in 2003 - even if it's in the back end, because both front and back ends are being executed locally, by whatever version of Access you use to open the front end.

In summary, I don't think the 2003+2007 idea will work - and trying harder won't make it work.

 

[Banana]

Are you likely to approach the database size limit (4GB, I think)? - it's not a massive technical problem because there is an upgrade path to the full version with unlimited db size - but that introduces a sudden large cost.

Not that I'm suggesting MS licensed products aren't worth the money, but if it were me, and I was starting with a reasonably blank canvas, I'd be considering one of the open source database solutions (in fact that is what I'm considering).
If I have to learn something, my inclination is to learn something I can scale up without paying out a lot.

However, SQL Server skills are more marketable in some contexts than, say, MySQL skills, so it's never a completely cut and dried decision.

FWIW - I did that myself, using MySQL for my old project as I used to work for a nonprofit who had neither war chest to finance the costs nor big irons to house the snazzy technology. I also didn't want to lock the company into a product that they may end up not being to afford, so that was a significant decision.

I'm sure Express Edition works well... provided that we factor in the hidden costs of the cap & limitations which may eventually force a upgrade to a standard license and this is desirable that company use the same brand for whatever reasons.

WRT marketability, I believe it's how well versed you are with SQL in general term that's far more important. In my current job, I end up working on SQL Server despite the fact that prior to the job I've never touched SQL Server but it was not too hard to adapt what I knew of MySQL to SQL Server and I was up and off running writing stored procedures and triggers.

I don't think I know of a DBA who's successful and knows only one dialect (proficient in only one, sure, but almost every DBA has had to work with more than one vendors)

 

[Banana]

I think this theory will prove incorrect - or rather, the individual files may retain their features, but a feature exclusive to Access 2007 will not work when you try to run it in 2003 - even if it's in the back end, because both front and back ends are being executed locally, by whatever version of Access you use to open the front end.

In summary, I don't think the 2003+2007 idea will work - and trying harder won't make it work.

Well, when I said it could work, I was in the rut thinking of 2003's .mdb linking to 2007 .mdb.

But that's basically correct, and is how we can use ULS or replication in 2007, provided that we use .mdb, because this is no longer supported in newer .accdb. As for the 2007 allowing linking .mdb to .accdb via UI, I'm suspicious about that because I'd bet we were using 2007's object library which is aware of both file formats but to 2003's object library, it knows only one format so if it see itself being linked to .accdb, it would shit a brick.

 

[Atomic Shrimp]

Well, when I said it could work, I was in the rut thinking of 2003's .mdb linking to 2007 .mdb.

But that's basically correct, and is how we can use ULS or replication in 2007, provided that we use .mdb, because this is no longer supported in newer .accdb. As for the 2007 allowing linking .mdb to .accdb via UI, I'm suspicious about that because I'd bet we were using 2007's object library which is aware of both file formats but to 2003's object library, it knows only one format so if it see itself being linked to .accdb, it would shit a brick.

Quite possible - although it might depend on how cleverly they have constructed the file - Access doesn't care too much about the file extensions and if the file header inside is designed in a modular way so that 2003 can get the bits it needs without tripping over the 2007 bits it won't understand, then it might work....

But what almost certainly won't work is any 2007-specific functionality, such as encryption.

 

[boblarson]

I don't see how you could link an ACCDB file from a 2003 frontend. The file type isn't even listed in the linking area as Access 2003 doesn't even know about the existence of these files.

AtomicShrimp:

You said:

Using 2007, I created an .accdb (2007) back end, then I created a .mdb (2002-2003) database and tried linking some of the 2007 back end stuff in.

But how did the ACCDB file format even come up in the linking dialog?

 

[Banana]

He said he did it in 2007, not in 2003, which was why I posted that since it was in 2007, I presumed we were using 2007's object library which knew about both files and speculated that it would break in 2003. But only one way to be sure: Actually try it and see what happens.