how to connect VBScript (Excel) to SQL Server web database (via IP)-CodePudding

I'm at my job trying to do some unknow stuff for me, you see, we're trying to connect an excel document with a VBScript Macro to a databse stored in web server but for some reason doesn't recognizes the user and throws an error repeatedly, i discarded a connection issue since it returns an SQL error instead of something like a timeout or server doesn't exists or something like that, we're trying to connect to the server using the ip address, we also checked that the logging method is on mixed (win and sql) and remotes connections to the server are enabled as well, also if i use the credentials provided in the connection string (username and password) i can actually log in to SQL Server without any issue, we also tried a direct connection (external vpn) because we thought it could be our firewall, but got the same error anyway, so we have no clue what it could be and we're kinda running out of ideas on how to do this, i'll post down below the code i'm using to trying the connection (obviously test data but similar to reality)

picture of the error i'm getting (don't post the original since it's in spanish but is very similar to this):

code i'm currently trying:

Sub excel_sqlsrv()
Set rs = CreateObject("ADODB.Recordset")
Set conn = CreateObject("ADODB.Connection")
strConn = "Driver={ODBC Driver 17 for SQL Server};Server=10.20.30.5;Database=mydb;UID=sa;PWD=abcd12345;"
conn.Open strConn
strSqL = "SELECT * FROM USERS"
rs.Open strSqL
End Sub

Any advice, tip or trick could be of tremendous help for me, i'll be looking forward to any kind of comment, thanks in advance

CodePudding user response：

Use the ODBC Data Source Administrator to create a connection named mydb and test it works. Then use

Sub excel_sqlsrv()

    Const strConn = "mydb" ' ODBC source
    Const strsql = "SELECT * FROM USERS"
    
    Dim conn As Object, rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    Set conn = CreateObject("ADODB.Connection")
    
    conn.Open strConn
    Set rs = conn.Execute(strsql)
    Sheet1.Range("A1").CopyFromRecordset rs
    
End Sub

CodePudding user response：

You can try using OLEDB provider instead of ADODB.