N
NJP
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
OLEDBatabase Password="";Jet OLEDB:Engine Type=5;Jet OLEDBatabase 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 OLEDBon'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 OLEDBatabase Password='';" & _
"Jet OLEDB:Engine Type=5;Jet OLEDBatabase 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 OLEDBon'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).FillAdjacentFormulas = True
Worksheets("MTL").QueryTables(1).CommandText = Sql
Worksheets("MTL").QueryTables(1).Refresh
Application.ScreenUpdating = True
End Sub
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
OLEDBatabase Password="";Jet OLEDB:Engine Type=5;Jet OLEDBatabase 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 OLEDBon'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 OLEDBatabase Password='';" & _
"Jet OLEDB:Engine Type=5;Jet OLEDBatabase 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 OLEDBon'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).FillAdjacentFormulas = True
Worksheets("MTL").QueryTables(1).CommandText = Sql
Worksheets("MTL").QueryTables(1).Refresh
Application.ScreenUpdating = True
End Sub