SQL Server Tables From Access

[jpinto1167]

What had been your experience about manipulating SQL Server tables from Access? Had been good or bad experiences? What are the limitations and pitfalls?

Thank you

 

[dhookom]

This has always worked well for me. If you are planning on using SQL with Access, there are several good books available.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BNPMike]

In case you weren't aware, Access now has a special relationship with SQL Server along the lines of the special one it has with Jet. Access apps of this type are called 'Projects'.

 

[RandyMyers]

ODBC connections to SQL Server also work extremely well.

I find using SQL Server as the database back bone and Access to develop the front end application works extremely well.

BTW- Access Project seems fine although I find that it has many limitations versus Access mdb.... both work fine for creating applications to SQL Server...

 

[jpinto1167]

Thank you for the opinions. Are there any dangers of using Access as the front end to manage the server tables?
If I create a copy of one of the tables and I do changes there like renaming the table, deleting data etc Does it impact the source table anyway?

Could I manage the stored procedures and triggers from Access or it has to be from the SQL server itself?

Thank you

 

[wmikeh]

Using the Access Project you can set up the stored procedures and triggers right from access. I have used both mdb files with an odbc connection to sql on the back end and adp files and find that both work very well. From what I have read thought it seems that Microsoft will not be making any updates or changes to the jet system and so the future of Access might be Access Projects utilizing sql server on the back end.

 

[jpinto1167]

Wmikeh,

That sounds very promissing. How do you work with the stored procedures? Do you write them into an Access module and then execute and saved them into SQL server? Do you know have any experience with MDE in the back end?

Thank you

 

[BNPMike]

Do you mean MSDE - aka SQL Server Desktop Edition?

 

[jpinto1167]

yes MSDE

 

[wmikeh]

Sorry... havn't been looking at the forums lately... In Access Project you just create the stored procedures like you would a query. The triggers are created by right clicking on a table name, selecting triggers and then creating a new trigger. Remember that you are working in transact sql when writing your queryies and triggers. I am currently working on database that uses msde as the back end. From what I understand, MSDE is exactally like SQL Server with out a lot of utilities. MSDE also contains a software governor that slows it down depending on how many concurrent connections you have open... At lease thats how I understood it hehe

 

[BNPMike]

MSDE is SQL Server. It is SQL Server Desktop Edition. As you say you don't get some of the bits.

 

[jpinto1167]

Will you have stored procedures and triggers with MSDE?

Thank you

 

[BNPMike]

http://www.microsoft.com/sql/msde/default.asp