1

I have been creating an access database for my assignment. I am using SQL Server 2014 for the datasource for the access tables. Now the problem is that according to the assignment

  • I need to make the db multiuser over network
  • it has to be an accde file

In short, how can I connect to SQL Server when I deploy the database on my college pc. And how to make the accde work on my school network where the SQL Server is not the one I am using at my personal machine.

It is a simple book entry database containing few tables including print button and picture of book attachment.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bitter Cherry
  • 11
  • 2

2 Answers2

0

Use a DSN-less connection. Provide a function that changes the connection string on all linked tables.

See Linked table ms access 2010 change connection string

or Changing SQL connection information for DSN-less Access frontend

You don't want each user having to enter the server information into the frontend. So take your frontend to the school network, run the function to re-link all tables, then create the .accde, then distribute it.

Andre
  • 26,751
  • 7
  • 36
  • 80
0

If the sql server is on your same network, then all you have to do is link your tables to the production sql server. You then crate the accDE, and that can be deployed to any machine.

Access by “default” creates a DSN less connection. So if you follow the defaults when linking to sql server (use a FILE dsn), then once access links to those tables, access “converts” the DSN to a DSN less connection for you.

What this means is that you do not need any VBA re-linking code. When using a file dsn, access converts these to DSN less for you. Once linked, then Access ignores and does not use nor require the original DSN.

So your steps are: Linking to the production SQL server on the network. Just make sure you use the “default” file dsn.

Once you are sure the accDB works with that production SQL server, you can then compile/create the accDE. That accde will now function on ANY workstation (and this work since access by default creates and uses and makes a DSN less connection for you).

You do NOT need any VBA re-link code.

You do NOT have to write some VBA code to create a DSN less connection (access does this automatic for you).

Of course, during this re-link process, your computer will have to be on the same network with the production SQL server you plan to link to.

If you were some off site worker, and you cannot be on the same network during the re-link process, then YES you would have to adopt some VBA code to re-link for you. However, if you can be on the same network during this re-link process, then at that point you can create the accDE file, and it can be distributed to any workstation on that network without further configuration.

About the only issue would be to ensure that you link to this production server using the standard and legacy “SQL Server Driver” as your linking choice. You can use the later “native 11” etc. drivers, but they are not installed by default on each version of windows and thus this ODBC driver may well not be installed on each workstation (but the standard SQL Driver is installed by default.

Again: You do not need some VBA code or re-link code to create a DSN-less connection. Access by default when choosing a “file” DSN will create DSN-links for you – you thus don’t need any special re-link code, nor do you need some VBA re-link code to achieve DSN-less links.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51