Excel VBA: SQL Connection Error (run-time error 2147217843 login failed"

[VAer]

It is excel VBA issue.

If I use Sybase connection string (another database from same screenshot of ODBC Data Source), and the code works fine.

But if I use the code for SQL Server, it returns error, see attached screenshot. I am sure that Server name and Database name are correct. Connection string looks correct too ( https://www.connectionstrings.com/mi...or-sql-server/ ). Did I use the correct connection string (standard security)? What could cause such error?

I am not an IT professional, so not sure why it has error. Is it because of database setting? Is it because it does not allow User/Password access? How does SQL server setting work? Or some kind of Excel reference?

I can manually use excel to connect the database and copy data to excel, see screenshot. So I do have the access to database.

Thanks.

Additional note: is it related to Microsoft Office? My workplace microsoft office may be 32 bits (not sure), while the database is 64 bits? Not an IT, don't know much about it. The reason I say this: when I use Access > External Data > ODBC Database to connect to the database, it shows 32 bit for the database. But when I am ODBC Data Source, it shows 64 bits, since it is 64 bit laptop.

Please Login or Register  to view this content.

[hrlngrv]

Disclaimer: I don't use SQL Server, so I have no specific 1st hand experience with it.

If your employer has SQL Server databases, presumably it also has an IT department. You might want to ask them whether there are any policies for SQL Server access which prohibit automated access, so only allow for manual, interactive access. If that's the case, and IT isn't willing to make an exception for you, then manual interactive access would be your only alternative. Better to find that out up front before spending more time trying to automate this.

OTOH, if IT does permit automated access, they'd almost certainly be able to provide more comprehensive details for doing so within your workplace environment than ANYONE here could give you.

Sometimes in-house resources should be your 1st recourse.