FrontEnd with Access & BackEnd with SQL Server???

[sandra45]

Hi, I have a database built in Microsoft Access 2000. As the requirements expand, I see the need to have the database stored and managed in SQL Server 2000. However, it is useful if I can keep the same interface for the users to work on because they are all now familiar with the look and feel under Access. Is it possible to have Microsoft Access as the front end and SQL Server 2000 as the back end, therefore I can still keep the interface? If yes, what steps that I need to do? Thanks, hope someone out there can help me.

Sandra

 

[Cenedra]

Yes.

1. Create a DSN for your SQL Server table.
2. In Access, right click on tables and select Link ... an open dialog will pop up and for file type, select ODBC connection.
3. Choose your the DSN you just created.
4. Import the tables you need.

Viola,
Therese

 

[sandra45]

Thanks, can you let me now more details pls, I'm a newbies here.

Does it mean that I have to write all codes in SQL Server then? What codes that will be left still in Access?

 

[Cenedra]

This means that your tables are linked to Access. Anything you do in Access effects your SQL Tables. Nothing more.

That is what you wanted, I assume?

 

[DrewConn]

You will also most likely need to change the SQL code in your queries you already have written to include the owner name for the table.

For example, once you have a table in SQL and you link to it in Access, say the table name Employee, Access will link in the table with the name including the owner, for example
dbo.Employee. So just add the dbo. before the table name in your existing queries in Access.

Have fun.

 

[ncedddf]

Actually, what DrewConn says is not necessary. Once you have linked the SQL Server tables, you can change the name in Access to match you Access codes, providing you don't already have an existing Access table with the same name.

 

[Cenedra]

thanks ncedddf, I thought that using dbo wasn't required, as I have never used it myself.

 

[mkelly11]

How familiar are you with SQK 2K? Are you looking to create the back end from scratch? Access does offer an "Upsizing Wizard" for SQL, which will automatically create and link your tables. I was in your same scenario about a year ago, if you have any other questions I'd be happy to help.

 

[Cenedra]

sandra45,

I'd take mkelly11 up on his offer for help upsizing your access database. The wizards are pretty good, especially for beginners and people just learning

I hope we have all helped!

 

[sandra45]

Dear All,

Thanks a lot, I don't know much about SQL Server. I'll try with the upsize wizard and I'll come back to you soon.

Regards,
Sandra