ADO connection with workgroup Access database

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




--
ca1358

Hi ca1358

Is your code working with other files ?
Is the database password protected

I have some information here
http://www.rondebruin.nl/accessexcel.htm


--
Regards Ron De Bruin
http://www.rondebruin.nl



"ca1358" <ca1358@discussions.microsoft.com> wrote in message news:98FF2A1C-0790-4BB9-91B9-90637D3DB780@microsoft.com...
>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
>
>
>
>
> --
> ca1358

I can use it with a non-password database
and a password protected (Add a password line)
but not workgroup database
--
ca1358


"Ron de Bruin" wrote:

> Hi ca1358
>
> Is your code working with other files ?
> Is the database password protected
>
> I have some information here
> http://www.rondebruin.nl/accessexcel.htm
>
>
> --
> Regards Ron De Bruin
> http://www.rondebruin.nl
>
>
>
> "ca1358" <ca1358@discussions.microsoft.com> wrote in message news:98FF2A1C-0790-4BB9-91B9-90637D3DB780@microsoft.com...
> >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
> >
> >
> >
> >
> > --
> > ca1358
>
>
>

Sorry never be able to test this
Post this in a Access newsgroup

--
Regards Ron De Bruin
http://www.rondebruin.nl



"ca1358" <ca1358@discussions.microsoft.com> wrote in message news:0D4CF60D-B649-4EE2-B34A-4FF24C7C7FC4@microsoft.com...
>I can use it with a non-password database
> and a password protected (Add a password line)
> but not workgroup database
> --
> ca1358
>
>
> "Ron de Bruin" wrote:
>
>> Hi ca1358
>>
>> Is your code working with other files ?
>> Is the database password protected
>>
>> I have some information here
>> http://www.rondebruin.nl/accessexcel.htm
>>
>>
>> --
>> Regards Ron De Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "ca1358" <ca1358@discussions.microsoft.com> wrote in message news:98FF2A1C-0790-4BB9-91B9-90637D3DB780@microsoft.com...
>> >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
>> >
>> >
>> >
>> >
>> > --
>> > ca1358
>>
>>
>>

You are not stating a UserID or Password in Conn.Open for one thing.
Assuming this is a typo, it would seem the error is telling your problem in
that the user does not permission to read that table.
Change the permissions for that user.

NickHK

"ca1358" <ca1358@discussions.microsoft.com> wrote in message
news:98FF2A1C-0790-4BB9-91B9-90637D3DB780@microsoft.com...
> 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
>
>
>
>
> --
> ca1358