It is excel VBA issue.
If I use Sybase connection (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 ( microsoft-odbc-driver-17-for-sql-server ). Did I use the correct connection string (standard security)? What could cause such error? Is it related to SQL database setting? Or some kind of Excel reference?
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?
Thanks.
Edit: 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.
Edit 2: I can manually use excel to connect the database and copy data to excel, see screenshot. So I do have the access to database.
Username = TextBoxUsername.Value
Password = TextBoxPassword.Value
Username = WorksheetFunction.Trim(Username)
Password = WorksheetFunction.Trim(Password)
'This is the connection string for MS SQL, which does not work.
ConnectionString = "Driver={ODBC Driver 17 for SQL Server};Server=myserver;Database=mydatabase;UID=" & Username & ";PWD=" & Password & ";"
'Below code is for Sybase database, which works.
'ConnectionString = "Driver={Adaptive Server Enterprise};server=myserver;port=20025;db=mydatabase;uid=" & Username & ";pwd=" & Password & ";"
'set logon details:
Set Cn = CreateObject("ADODB.Connection")
'Connect to Database
Cn.Open ConnectionString
Unload Me
Form2.Show