multiuser with sql server

[ain79]

hello
i am trying to learn how top use access and sql server togather .
i had developed a simple multiuser application using access, now i just want it to be a multiuser but based on sql server and access.

now if u can tell me what to do if i want to link the tables from a network share computer or serever

thanx

 

[FancyPrairie]

If you want to link the tables that reside on the SQL server, you do so by using the SQL ODBC driver and setting up a DSN. You have 3 choices on the DSN: User DSN, System DSN, File DSN. I prefer the File DSN, then I don't have to define the DSN on every machine I put my application on.

To link to a table on the SQL server, do the following:

1. Goto FILE | GET EXTERNAL DATA | LINK TABLES...
(The link dialog box will now appear)

2. In the Link dialog box, select ODBC Databases in the "Files of Type" dropdown box. Then create a New DSN which points to your SQL server.

 

[ain79]

thnax for ur attention
2 problems
1: when i try to do what u said it gives me this error

"you cannot use odbc to import from ,or link an external microsoft jet or isam database table to ur database "

2: how if database is on other computer on network share then just i have to make file dsn and then ......please
if u can give me some steps i will be gr8full"

thanx

 

[FancyPrairie]

I'm comfused as to what you are trying to do. If you want your front-end to access tables that reside on a SQL server, then you need to set up a connection to the tables. You can either establish the connection at runtime via code or you can set up a DSN and link to the tables via ODBC.

However, if you want an Access database (back-end) to reside on a server then all you have to do is link to those tables. (But use a url rather than a specific drive letter.)

 

[ain79]

i want my front-end to access tables that reside in a sql server .

and i want help in how to create dsn and link table via odbc

 

[mfd777]

To set up SQL server link do the following:

1. Ensure tables exist in sql server
2. Open ODBC administratot (control panel, if using win2k then administrative tools section)
3. Create a new dsn type of your choice (user, system or file). Click on add. Select microsoft sql server driver (probably version 3.7 as the driver (usually last one on the list).
4. Enter in a name and description of your choice (this is what you will select from within access to link to).
5. Select sql server from drop-down list or type in server name. Choose security login required (sql server or trusted)
6. On the next tab change the default database to the name of the database you wish to connect to. Click nect and then finish.

7. Go to access. Click on file --> get external data--> link tables. On the files of type drop-down select ODBC Databases()
8. Select dsn that you have created and say o.k.
9. Depending on security selected, SQL Server may now prompt for password.
10. All tables will be displayed.

Select tables that you want to link. They will all be prefixed with the tabel owber name. You may rename after linking.

 

[ain79]

thanx alot man
u were realy helpful
it did the trick