Macro retreiving data from SQL database through Access Linked tables and queries

[Frederik_C]

Hi all,

I'm new to this forum and I'm struggling with a problem in one of my Excel VBA macro's.

I would like to retreive data from a MS access query, that on its turn is connected via ODBC to an SQL server.

At this moment I'm able to get the data in my excel sheet via a macro, but each time I run the macro I need to enter the SQL database user profile and password, I would like to automate my macro in this way that I can put the profile and password within the macro.

The code I'm using at thie moment looks like this:

Sub importdatafromaccess()

Static acc As Access.Application
Dim strDbName As String

strDbName = "L:\ELCBEBR\DEP-IT\Itrqst\ITR-2000-v2.mdb"

Set acc = New Access.Application
acc.Visible = True
acc.OpenCurrentDatabase (strDbName)
acc.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TZcopyleadtime", _
"C:\Temp\TZcopyleadtime.xls", True
acc.Quit
Set acc = Nothing

Workbooks.Open "C:\Temp\TZcopyleadtime.xls"
Windows("Support Officer v-5.xls").Activate

End Sub

Could anyone help me automating this macro to put the profile and password coded into this script please.

Thanks in advace,

Frederik