HELP Please. Access + SQL

[Thinker]

Hi

Apologies as this has probably been asked before, but currently sat at work and under pressure to specifiy the right solution.

I've been using Access for years but right now have a need to implement a much more robust and strategic solution. As such we plan to use an SQL Database as our primary data platform from which will will develop a range of Access applications that will process and report on that data.

I know that I can link an Access front-end App to an SQL database though I have only ever done that on a read-only basis. Now I will need to be able to read and write so that I can develop some functional multi-user data entry/management applications.

Currently I use Access databases with separate front-ends for this and naturally I have the flexibility to be able to maintain the data model, creating tables, fields etc as and when I need to.

My belief (and worry) is that if we switch to an SQL database platform, I will no longer be able to create/edit tables and fields using Access. The database will thus be "locked" and the only way to change it will be down at the SQL level itself. Thus every time I want to make a change I'd need an SQL DBA type guy to make those table/view changes for me. That sounds horredous in terms of flexibility and practicality.

Is there any way at all that I can manage and maintain an SQL database using Access itself? I have read a little about MS Access Projects which use OLEDB connection rather than ODBC connection. Would this allow me to manage the SQL data model from within Access?

If not, what do people usually do in this position? Do we lose all of our lovely speedy development flexibility once we migrate to an SQL database? Do I have to train up to be an SQL DBA myself so I can manage my own SQL DB?

All feedback welcomed,

Many Thanks

Nigel

 

[pilsdumps]

Yep, you can create and edit SQL Server tables etc using Access although there will be issues with compatability depending on the versions of each. For instance, Access 2003 cannot amend SQL Server 2008 tables etc, although you can see the data ok.

There are other differences - if I remember correctly, queries are replaced by stored procs, functions etc.

If I were you, I'd download a copy of SQL Server 2008 express making sure it includes Management Studio and get writing T-SQL. Having developed Access databases for a number of years before using SQL Server, I reckon you'll never look back when you get started. I hate using Access now, particularly the querying functionality - SQL is a thousand times better. I have also used Access as a front end to SQL (but only when I have to - using .Net and WPF in particular is far superior), and would always use Management studio for any backend development.