ADO connection with workgroup Access database

G

Guest

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
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
R

Ron de Bruin

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

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



ca1358 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
N

NickHK

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ADO - Access to Excel 5
ADO retrieval from close workbook-data type issue 2
A Script to Create a Recordset and Import Data to a Spreadsheet 7
Trying to write a query 1
Compile Error\Interacting with Access 16
SQL statement returns zero records 1
Running SQL queries for Access using Excel VBA 1
Getting records from Access 97 to Excel 97 3

Top