Question Using Access 2007 as a front end to MSSQL

[Bombbastic22]

I have received orders from above to take an access 2007 database and have it sit on top of an mssql database.

It's proving to be a serious headache and I'm not sure about the practicalities of it.

Any advice on same would be super.

Thanks in advance fellow geek a zoids!

 

[jdraw]

By mssql do you mean MS sql server?
If so, there is a power point presentation by Armen Stein
Microsoft Access MVP

here http://www.jstreettech.com/files/Best-of-Both-Worlds.zip

 

[DavidAtWork]

We have several Access front-end, sql server backend databases and they're quite stable. The backends are on a network drive and the performance is very reasonable albeit we're not talking millions of records, but certainly in the 100,000's our frontends are Access 2003 however but I can't imagine 2007 ver would be much different.
What sort of advice are you seeking?
David

 

[Bombbastic22]

We have several Access front-end, sql server backend databases and they're quite stable. The backends are on a network drive and the performance is very reasonable albeit we're not talking millions of records, but certainly in the 100,000's our frontends are Access 2003 however but I can't imagine 2007 ver would be much different.
What sort of advice are you seeking?
David

Hey thanks for the reply. I was feeling pretty lost when I posted that. Just started a new job and the boss wants me to work wonders with software and such that I've never used before.

I got there anyway - SSMA is a great tool for this purpose. I can't believe it works so nicely and links directly to current forms. Ah well, you live and learn I guess.

 

[Bombbastic22]

I wonder though - would there be any drawbacks to using multi field primary keys when you port access tables into mssql?

I was trying to increase usability and as it's not a huge database, I thought it would be handier for the user than having to go looking for ids. It's one of those tables you use to break up a many to many relationship, I can't think right now what you call them.

Any input on this would be super!!

 

[gemma-the-husky]

i dont understand this - what has looking for ids? got to do with the "host" back end

 

[Bombbastic22]

i dont understand this - what has looking for ids? got to do with the "host" back end

Lol. I guess I was a little lazy in my explanation there. Sorry about that.

There is just a little bit of access functionality that I'm thinking may cause me headaches down the road............due to it possibly not working in the same way when the database has ms sql in the back end as opposed to access....

It doesn't matter though.....If it happens to be an issue - sure that's time enough to be posting in forums about it, though it will probably be an easy enough fix anyway.

Ta.

 

[DavidAtWork]

"It's one of those tables you use to break up a many to many relationship" .. Does sound like your table structure might need an overhaul as doesn't sound like it's properly normalised.
You may experience problems when trying to link Access to sqlServer, if you your tables do not have defined primary keys.
David

 

[Bombbastic22]

"It's one of those tables you use to break up a many to many relationship" .. Does sound like your table structure might need an overhaul as doesn't sound like it's properly normalised.
You may experience problems when trying to link Access to sqlServer, if you your tables do not have defined primary keys.
David

It links lovely actually. .............it's kind of a referential wizard thing that you use to pull in the contents of fields in other tables so the user can select the person they want to add to the form instead of plonking in an id which references another table. I came accross it online and it seems handy enough considering there aren't a huge amount of records and I thought it would increase usability.

Maye I'm missing something?