C
ca1358
I am working in excel and trying to access a Access workgroup database.
I have this piece of code and I get an error “Records can not read; No
permissions on Table1†at this line:
“ .Open Source:=Src, ActiveConnection:=connâ€
but I have this piece where the user info is held
.Properties("Jet OLEDB:System Database") =
"\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Jennifer\NEW082804.MDW"
Any help would be greatly appreciated!!
///////////////////////////////////////////////////////////////////////
Private Sub CommandButton1_Click()
OpenADO
End Sub
Public Sub OpenADO()
Dim dbpath As String
Dim Src As String
Dim conn As ADODB.Connection
Dim Col As Integer
Dim Recordset As ADODB.Recordset
Dim As400 As Integer
Dim A1 As Range
dbpath = "Data Source=\\Dtcnas-ilsp002\mandatory\Analysts - Working
Files\Carol\Demo\trade limit.mdb;"
Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Jet OLEDB:System Database") =
"\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Jennifer\NEW082804.MDW"
.Open dbpath
End With
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter
Src = "SELECT Table1.[AS400 ID], Sum(Table1.LoanAmt) AS SumOfLoanAmt "
Src = Src & "FROM Table1 "
Src = Src & "WHERE Table1.PoolCode NOT IN ('GOVT', 'G2BD', 'G21A', 'G23a',
'G25A') "
Src = Src & "AND Table1.RecDt Between #6/19/2006# And #6/23/2006# "
Src = Src & "GROUP BY Table1.[AS400 ID] "
'Src = Src & "HAVING Table1.[AS400 ID]=" & [forms]![payup table]![AS400 #] &
";"
.Open Source:=Src, ActiveConnection:=conn
' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Sheet1.Range("a1").Offset(0, Col).Value =
Recordset.Fields(Col).Name
Next
' Write the recordset
Sheet1.Range("a2").Offset(0, 0).CopyFromRecordset Recordset
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set Recordset = Nothing
conn.Close
Set conn = Nothing
End Sub
I have this piece of code and I get an error “Records can not read; No
permissions on Table1†at this line:
“ .Open Source:=Src, ActiveConnection:=connâ€
but I have this piece where the user info is held
.Properties("Jet OLEDB:System Database") =
"\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Jennifer\NEW082804.MDW"
Any help would be greatly appreciated!!
///////////////////////////////////////////////////////////////////////
Private Sub CommandButton1_Click()
OpenADO
End Sub
Public Sub OpenADO()
Dim dbpath As String
Dim Src As String
Dim conn As ADODB.Connection
Dim Col As Integer
Dim Recordset As ADODB.Recordset
Dim As400 As Integer
Dim A1 As Range
dbpath = "Data Source=\\Dtcnas-ilsp002\mandatory\Analysts - Working
Files\Carol\Demo\trade limit.mdb;"
Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Jet OLEDB:System Database") =
"\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Jennifer\NEW082804.MDW"
.Open dbpath
End With
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter
Src = "SELECT Table1.[AS400 ID], Sum(Table1.LoanAmt) AS SumOfLoanAmt "
Src = Src & "FROM Table1 "
Src = Src & "WHERE Table1.PoolCode NOT IN ('GOVT', 'G2BD', 'G21A', 'G23a',
'G25A') "
Src = Src & "AND Table1.RecDt Between #6/19/2006# And #6/23/2006# "
Src = Src & "GROUP BY Table1.[AS400 ID] "
'Src = Src & "HAVING Table1.[AS400 ID]=" & [forms]![payup table]![AS400 #] &
";"
.Open Source:=Src, ActiveConnection:=conn
' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Sheet1.Range("a1").Offset(0, Col).Value =
Recordset.Fields(Col).Name
Next
' Write the recordset
Sheet1.Range("a2").Offset(0, 0).CopyFromRecordset Recordset
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set Recordset = Nothing
conn.Close
Set conn = Nothing
End Sub