0

I am really stumped on this, I am not sure if someone can at least point me in the right direction?

I have an MS Access Reporting Application. It has a few local tables in it but predominantly relies on tables from a SQL Server 2005 database that it links to. It links to the SQL DB via a System DSN.

I have built a function that confirms the required tables are linked before any reports are run and that all works great. I have a stored Connection String for each time the system requires a connection to the SQL DB to refresh the linked tables. However, when the user FIRST runs the app, a SQL Authentication Dialog Box pops up asking the user for a Username and Password to the requested Database.

No matter what I do, I can't seem to suppress this. The System DSN is and must be set up for SQL Authentication, however if I could just suppress this initial dialog box, my code will take care of the Authentication moving forward.

Can ANYONE help me with this? I am stumped.

Thank you so much in advance for all suggestions!

Dallas

Cade Roux
  • 88,164
  • 40
  • 182
  • 265

4 Answers4

2

Uh, is there some important reason why you're using SQL authentication instead of Windows authentication? The latter makes it completely unnecessary to store any user information in your connect strings.

David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58
1

Have you tried putting the SQL user name and password in the connection string?

User Id=myUsername;Password=myPassword;
dsteele
  • 1,282
  • 1
  • 14
  • 25
0

uh, if you're reporting on SQL Server data.. why are you using Access at all?

SQL Server comes with a MUCH superior reporting system called 'SQL Server Reporting Services'.

SQL Server is easier to use than MS Access, once you get the hang of it!

It's just simpler to go directly against SQL, right?

In Reporting Services, there are a handful of ways to prevent SQL Authentication prompt.

Aaron Kempf
  • 580
  • 2
  • 11
0

If any of your code is trying to access those tables (even a drop-down on a hidden form) prior to your table-checker running, it will try and authenticate.

Your verification code could itself be the culprit. Many try to verify by checking if they can retrieve records. Instead, verify that the connection string is as it should be in the schema definitions: CurrentDb.TableDefs("MyTable").Connect

Tom Mayfield
  • 6,235
  • 2
  • 32
  • 43