Selection.QueryTable.Refresh

[Kathleen_0422]

Hello

I am trying to write a Workbook.open event and I need to refresh a query table that taps into a SQL server.

How do I pass the User ID and password?

Thanks in advance for any help.

 

[java568]

I believe you do it within the connection string:

Dim con As connection 
Set con = New connection 
con.ConnectionString = "ODBC;DSN=DB01;UID=LOGIN;PWD=PASSWORD;Database=MyDatabase" 
 
Worksheets("Received").QueryTables.Add(Connection:=con, Destination:=Worksheets("WS").Range("A1"), SQL:=SQL).Refresh 
 
con.close 
set con = nothing

 

[Kathleen_0422]

Thanks but......

Do I need to set a reference up? If so which one? I am getting an error when I try to run stating:

User defined type not defined

 

[java568]

I'm not sure, can you run the quertytable connection manually with the VB recorder on and see what it says?

 

[Kathleen_0422]

Thanks, tried that but the query table is very large and when I record the macro it gets an error with the recorder. Data is returned but the recorder has red lines.

If I try to record a macro to simply refresh credentials do not appear in recorded content.

 

[java568]

Thanks, tried that but the query table is very large and when I record the macro it gets an error with the recorder. Data is returned but the recorder has red lines.

If I try to record a macro to simply refresh credentials do not appear in recorded content.

And it doesn't work when you insert the reference in DB01?

 

[Kathleen_0422]

No inserting that code causes it to come back with the User Defined data not defined.