When importing query from code - password required but not directl
 

I'm sure I missed something in the connection or sql when translating from
the edit query. This works without a password until I try to run via code -
which allows for connection to other instances of the same database. The
datarange property for save password is checked.

I do not want the pasword. And cannot figure out which one it's asking for.

Help is aprreaciated,
Nita
====================
From the Edit query
Connection:
Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=MTL_User;Data
Source=\\seasv01\eo_eg\Databases\MTL\MTL.mdb;Mode= Share Deny None;Extended
Properties="";Jet OLEDB:System
database=\\seasv01\eo_eg\Databases\MTL\MTL.mdw;Jet OLEDB:Registry Path="";Jet
OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk
Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System
Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale
on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False

Command type SQL

Command text
SELECT DISTINCT tblMasterTaskList.Activity_Desc, tblLinkedDocument.EHSID,
tblLinkedDocument.strFileSpecification FROM tblMasterTaskList INNER JOIN
tblLinkedDocument ON tblMasterTaskList.Task_ID = tblLinkedDocument.Task_ID;

my vba

Sub updatedata()
'import data from a selected MTL
Dim strConn As String, strMdw As String


strDBLocation = Range("a1")
strMdw = Left(strDBLocation, Len(strDBLocation) - 1) & "w"

Application.ScreenUpdating = False



strConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=' ';" & _
"User ID=MTL_User;Data Source='" & strDBLocation & "';Mode=Share Deny None;"
& _
"Extended Properties='';Jet OLEDB:System database='" & strMdw & "';" & _
"Jet OLEDB:Registry Path='';Jet OLEDB:Database Password='';" & _
"Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;" & _
"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;"
& _
"Jet OLEDB:New Database Password='';Jet OLEDB:Create System Database=False;"
& _
"Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on
Compact=False;" & _
"Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"

Worksheets("MTL").QueryTables(1).Connection = strConn

Sql = "SELECT DISTINCT tblMasterTaskList.Activity_Desc,
tblLinkedDocument.EHSID, tblLinkedDocument.strFileSpecification" & _
"FROM tblMasterTaskList INNER JOIN tblLinkedDocument ON
tblMasterTaskList.Task_ID = tblLinkedDocument.Task_ID;"

Worksheets("MTL").QueryTables(1).AdjustColumnWidth = False
Worksheets("MTL").QueryTables(1).FillAdjacentFormu las = True
Worksheets("MTL").QueryTables(1).CommandText = Sql
Worksheets("MTL").QueryTables(1).Refresh

Application.ScreenUpdating = True
End Sub
--
Nita J. Perez