How to suppress SQL Server Login prompt if append/connection fails?

Anonymous

I have been figuring out how to link my SQL Server back end tables to my Access front end using VBA (not via DSN). I think I worked all of that out in the following post:

https://answers.microsoft.com/en-us/msoffice/forum/msoffice\_access-mso\_winother/how-to-connect-access-front-end-to-sql-server/fe7ff42a-f21d-4ffa-bf1a-bb961064f575

I have it up and running, and it works great. I'm just thinking through how to handle some errors, and have worked through a lot of that. But one that I haven't figured out how to handle is if the SQL Server connection fails. When I test this (by putting in an incorrect password),

And then, it dumps them out into a dialog box where they can try new passwords!

I don't like this at all! How can I suppress these dialog boxes?! I already have error handling defined and SetWarnings = False. My full code (simplified/censored for posting) is as follows:

Public Function CreateSQLLinkedTable(strSourceTableName As String, strNewTableName As String) As Boolean

On Error GoTo OnError

Dim tdf As TableDef

Dim strConnect As String, strMsg As String

Dim fRetval As Boolean

Dim myDB As Database

' turn system warnings off

DoCmd.SetWarnings False

' set database vars

Set myDB = CurrentDb

Set tdf = myDB.CreateTableDef(strNewTableName)

' define connect string and source table

With tdf

.Connect = "ODBC;Driver={SQL Server};server=MyServer;database=MyDatabase;uid=MyUserID;Pwd=MyPassword;"

.SourceTableName = strSourceTableName

End With

' execute appending the table

myDB.TableDefs.Append tdf

' set bool var to True

fRetval = True

' turn system warnings back on

DoCmd.SetWarnings True

ExitProgram:

' this block of code will run if there are no errors

' set the return value = to fRetval (which was set to True above)

CreateSQLLinkedTable = fRetval

Exit Function

OnError:

MsgBox "There was an error connecting to the SQL Server data source. Error = " & Err & ", Description: " & Err.Description

'exit

Call CloseFormsAndQuit

End Function

Thanks!

Emily

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous

What is your connection string? You can't really use the same one you're using for relinking to make an ADO connection. I use this resource:

https://www.connectionstrings.com/access/

FWIW, I've used this method often to ensure that my connections are live. It will work, if your connection string is correct.

Sorry about the ConnectionString snafu - mine was air code as well.

0 comments

8 additional answers

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more
Anonymous

2016-10-05T15:18:53+00:00

Hi Scott,

I had replaced my connection string, but it was the same as the string I was using the link the tables which it sounds like I would need to tweak to work in this context. Before I could troubleshoot that, I got the DAO approach to work that Albert suggested. I have no reason to expect your approach wouldn't work if I took more time to figure out my connection string, so I'll marke this as an answer also.

I may also take the time to work the connection string out in the future, because your method has the advantage that it identifies the specific error(s). In all of my testing of the DAO approach, it always gave me a general connection error regardless of the cause of the error. It seemed like your approach would scroll through all of the errors and I could display all of them to help the admins figure out the problem. So I may switch to that once I have some time to work that out.

Thanks!

Emily
Was this answer helpful?

0 comments No comments
Anonymous

2016-10-04T23:31:50+00:00

Hi Scott,

I have the reference to the Microsoft ActiveX Data Objects library, but this still doesn't seem to work for me. The connection always fails, even if the connection string is fine, with the following error:

Error:--2147467259

Description: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

I'm not using a DSN. Does the ADBDB approach only work if you have a DSN?

The one difference between the code I tested and the code you suggested is that I had to reference con.ConnectionString instead of con.Connection. There was no con.Connection attribute for the con object.

Thanks!

Emily
Was this answer helpful?

0 comments No comments
Anonymous

2016-10-04T23:24:26+00:00

Thanks Albert,

I'm not sure why, but this code doesn't seem to trip any errors. Even if I completely mess up the connection string so that it is nonsense, it still steps right through to the qdf.Execute line, and never has any errors. Any ideas why that might be?

Thanks for your suggestion on the password. That was interesting to read, and I'll keep that in mind for the future, but for my current project I have to include Uid and Pwd in the front end, unfortunately. I don't want the users to have that info, as they all have SQL Data Tools and could climb directly into the back end data if I gave them their own Uid and Pwd. But I'm not protecting anything terribly valuable here. Just don't want it to be terribly convenientfor anyone to poke around and (probably accidentally) screw up the back end, as they could right now by just finding the mdb file on our network drive and playing around with it. The deployed front end is encrypted though, so if they want to get a supercomputer an crack the encryption, more power to them.

Thanks!

Emily
Was this answer helpful?

0 comments No comments