-2

I am attempting to use a Packaged Solution for my Access 2010 application that has its backend linked to SQL Server. At the moment, I'm using the .accdb file as the frontend, and I would like to distribute my application to some other Windows computers, but the Packaged Solution does not work. I had the package include Access Runtime, so their version of the frontend is running on Runtime and not full Access. However, once the application makes a request to the backend, the application does nothing, as I am not even prompted for the SQL Password as per usual with the full version. I've read on about including a .dsn file in the package can secure the SQL connection (see here), but going through steps of other tutorials to create .dsn files hasn't led to any results. Would anyone know how to correctly generate the .dsn file or if I've done something else wrong at this point?

(And yes, I understanding using Access 2010 in the year 2019 is almost a joke at this point, but I'm doing this for testing purposes. I plan to completely remake the frontend in Angular in the future.)

One other unrelated note... would it be a better idea to have the frontend hosted as a .html file like through the "Publish to Access Services" process? I did read that Access Services was discontinued last year, so would that not be possible?

Edit: This is not a duplicate of "DSN Less Connection (MS Access to SQL2016)" because A) I want to utilize a DSN Connection, not DSN-less and B) I am not using connection strings in my code to hook up with SQL.

Danchat
  • 137
  • 2
  • 12
  • If you're just doing this for testing, wouldn't it be better to use a full-fledged version of Access? – Robert Harvey Apr 01 '19 at 19:03
  • Also, it's unclear to me what, if anything, you might learn from this exercise once you start writing an Angular frontend. – Robert Harvey Apr 01 '19 at 19:04
  • Perhaps it would be more useful in full Access, but I thought it'd be quicker just to include an installer for Access Runtime instead of having the computer download full Access. And for the second comment, I'm testing to see how the backend data requests will work with the SQL Server. Prototyping in Access is quicker and easier to do than in Angular. – Danchat Apr 01 '19 at 19:23
  • Kinda depends on what your goals are. If you're distributing this, it's not really "for testing purposes," is it? – Robert Harvey Apr 01 '19 at 19:25
  • 1
    A word of advice, from someone who's been there: don't go to the trouble of using the Access Runtime to distribute an Access app unless you intend it to become the *actual application.* – Robert Harvey Apr 01 '19 at 19:26
  • 1
    Possible duplicate of [DSN Less Connection (MS Access to SQL2016)](https://stackoverflow.com/questions/44729231/dsn-less-connection-ms-access-to-sql2016) – Erik A Apr 01 '19 at 19:52
  • My best guess is you want to make a DSNless connection. Tip: if you don't want to mess with connection strings, using a file DSN also creates a DSNless connection. – Erik A Apr 01 '19 at 19:53

1 Answers1

1

You should be able to just create FILE dsn, link your tables, and then distribute the compiled accDE to each desktop.

However, what SQL odbc source provider did you use? If you use the SQL server ODBC provider, then that is by default installed on each computer.

However, if you linked using Native 11 (or later), then that driver is NOT installed on each workstation by default. So, I HIGH recommend you create a FILE dsn (not a user or system DSN), and link the table using that. (Access will create DSN-less links for you)

And you should NOT be seeing a logon prompt with your application. This suggests you forgot or missed the save password option.

So, I would re-link your tables, creating a new FILE DSN. And if you using the linked table manager, then make sure you check the prompt for new location to force creating of a NEW DSN. If you just re-fresh, then you DO NOT get a chance to click on the save password option during the linking process.

So, what odbc driver are you using? The native 11 or later are better, but they are not installed by default on each workstation. However, CAUTION is required here, since the older sql driver does NOT support the newer datetime2 formats. If you used these newer sql column types, they will be returned as string data types in Access and create a mess of issues.

So, first, I would re-link using a FILE dsn. Make sure you check the save password during the re-link.

You then compile your accDB into an accDE, and then distribute that. You don’t really need to use the package wizard, since once each workstation has the runtime installed, then a simple copy of the accDE to each person’s computer will thus work fine. There is NO special connection between your accDE and the package wizard. Once the runtime is installed, then any and all mdb, accDB, and your accDE can simply be clicked on to launch + run. So for testing, you can skip the package wizard, and just copy the accDE to the target machine, click on it, and see if it works.

Edit

The prompt and check box during this process is this:

enter image description here

So you have to check that box to save the password. Note that you ONLY get this dialog WHEN you create a new FILE dsn.

Community
  • 1
  • 1
Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • I linked to SQL Server through the "SQL Server Migration Assistant for Access". I see a logon prompt for my full access version, but I don't remember a "save password" option in the migration process. I'm using a Native 10 ODBC driver. Perhaps the migrant assistant is what's got me messed up. I'll try re-linking with a file DSN and I'll report back on that. – Danchat Apr 02 '19 at 15:00
  • Well, you already have all your linked tables, so that is good and fine. What you have to do is re-link again using the linked table manager in Access. If you [x] (check) the box prompt for new location, then you are able to create a new FILE DSN. The problem you have is you used the native 10, and that is not installed on each machine. And worse, by using native 10, then SSMA will have used the newer data types. So, I would likely consider re-migration, but use the legacy SQL driver, or you be forced to install native 10 on each workstation. – Albert D. Kallal Apr 02 '19 at 17:11
  • Well, I do have a Native 10 driver, but I don't remember specifying during the migration to use it. I just went through all the migration steps again, and there was no point where I chose what driver to use. – Danchat Apr 02 '19 at 18:29
  • Albert, I tried creating a new DSN file through the Linked Table Manager, but the process fails to work. I was able to create a DSN file, but when I hit the "OK" button I am prompted to put my SQL username and password in again. Once I do that, the Select Data Source dialogue box reappears, as if nothing happened. I'm not sure what to do next, but I now understand that part about selecting the SQL driver. – Danchat Apr 05 '19 at 16:04
  • see my edit and picture in my answer as to where that check box is to save you password. You must select this box. – Albert D. Kallal Apr 05 '19 at 18:04
  • The sql migration assistant for Access will choose the newer native drivers based on the sql server version you select, but you BETTER change the field mappings for date time from datetime2 to datetime before you migrate, else you be forced to use the native 11 drivers (the datetime2 columns will appear as text, and that is a HUGE mess to deal with, or you install native 11 drivers on each workstation) You MUST set the mappings in SSMA to datetime if you going to use sql driver as opposed to native 11 drivers. SSMA does not prompt to save passwords, you need to re-link to save passwords. – Albert D. Kallal Apr 05 '19 at 18:15
  • Thanks for the help so far. I've re-linked the tables and saved the passwords (it seems the migration process did not have a "save password" option) and saved the document as a .accde file. On the secondary computer, I got a "--ODBC call failed SQL Native 10 driver" error... I'm going to take another look at this on Monday and I'll report back here. – Danchat Apr 05 '19 at 22:02
  • Did you install the native 10 odbc driver on that other workstation? As I stated, you either link using the standard sql server driver, or if you choose native 10, 11 etc, but then you have to manually install those drivers on each work station. However, as I also pointed out, if you use SSMA, and you ALSO want to use non native drivers, then you have to change the field mapping in SSMA, else it will use datetime2 type columns which are NOT supported with sql driver (but is with native 10, 11 etc.). You also can use SQL table designer to change the column types if you don't want to re-migrate – Albert D. Kallal Apr 05 '19 at 22:16
  • And no, SSMA does not change or create accDE file. You can only create those if you are using the package wizard, but that is another issue RATHER far down the road and VERY separate from doing a migration of data tables to SQL server. – Albert D. Kallal Apr 05 '19 at 22:24
  • I used the standard SQL driver and it worked this time. Thanks for all the help, Albert! The final process turned out to be 1) use SSMA to migrate and create the tables in SQL, 2) delete all linked tables within Access, 3) relink tables using a created .dsn file with generic SQL driver to the previously created tables, 4) save the file as a .accde and distribute it with Access Runtime. – Danchat Apr 08 '19 at 14:39
  • You can also re-link and check the prompt for new location, and that will thus eliminate the need for deleting the tables. Also, beucuase you used SSMA, then you MUST look at and determine that you don't have any datetime2 data types on sql server, since those columns will appear as text columns in access and not datetime in Access. So things will seem to work, but you find tons of date/tme code and fields on forms will not work unless you correct this issue. Anyway, it sounds like you are up and running. – Albert D. Kallal Apr 08 '19 at 20:18
  • Yup, I'm currently working on a solution to the datetime2 problem SSMA created. Thankfully there's only a handful of dates used in my forms to fix. – Danchat Apr 08 '19 at 20:36
  • 1
    You NEVER in any circumstances will want to fix code or modify your application due to a datetime2 issue. The steps to fix this in general are you change the datetime2 to a datetime in the sql designer, and then re-link the tables on the access side. If you have to do anything more, or modify code or go on a code witch hunt in your forms then you busting up good code and forms, but are fixing something that will now break when you doo this correctly! That code should work100%. Make sure in access when you flip the linked table into design mode shows a date/time column data type. – Albert D. Kallal Apr 09 '19 at 19:10