ADO Resource
 

I need to find a book dedicated to Using ADO. I am importing information from
Access to Excel using VBA. I am dealing with one database that has just has
a password and the second one dealing with Workgroup Security with Security
put on by Security wizard. Could you recommend a book or detail web site
dealing with this. I have done research on the web and found nothing. I
must be putting the wrong key words. John Walkenbach states in his books
there are some out there. Any suggestion would be appreciated!!
--
ca1358

ADO Resource
 

Post your question here, getting data from Access is generally pretty
simple. I don't know about Workgroup Security, but someone else might.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"ca1358" wrote in message
...
I need to find a book dedicated to Using ADO. I am importing information
from
Access to Excel using VBA. I am dealing with one database that has just
has
a password and the second one dealing with Workgroup Security with
Security
put on by Security wizard. Could you recommend a book or detail web site
dealing with this. I have done research on the web and found nothing. I
must be putting the wrong key words. John Walkenbach states in his books
there are some out there. Any suggestion would be appreciated!!
--
ca1358

ADO Resource
 

I made a test database with no password and security and the code worked.
Then I just put the security workgroup and security by using the wizard and
I get this error: "runtime error 3112, No read permissions.

I made a test database with a password only and I get an error on this line:
Cnct = Cnct & "Data Source=" & DBFullName & ";" & "User
=mandatory;Password=;"

One of the databases that I have to deal with has a password.
Second one the person used workgroup and security wizard. She did make me
an admin.

I been trying for two weeks to gets this to work. I researched everything I
can think of on the web and been to the local Banres and Nobles.

Any help would greatly be appreciated or any resource!!


This is my Code I am using for the that has password:
Sub ADO_Demo()
' This demo requires a reference to
' the Microsoft ActiveX Data Objects 2.x Library

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim AS400 As Integer
Dim A1 As Range

' Cells.Clear
' MsgBox "Updating Incentive."

' Database information
DBFullName = ThisWorkbook.Path & "\Volume.mdb"

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";" & "User
Id=mandatory;Password=;"

Connection.Open ConnectionString:=Cnct




' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter
' Src = "SELECT [V05 CONV] ,[V05 GOVT], [Yes incentive] FROM [End of
year incentive] WHERE AS400 = " & TradeLimit.ComboBox1.Value

Src = "SELECT [V05 CONV] ,[V05 GOVT], [Yes incentive] FROM
[End_of_year_incentive] WHERE AS400 = " & TradeLimit.ComboBox1.Value

.Open source:=Src, ActiveConnection:=Connection

' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Sheet2.Range("a20").Offset(0, Col).Value =
Recordset.Fields(Col).Name
Next

' Write the recordset
Sheet2.Range("a21").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub


This is the code I am using for workgroup and security wizard security.
Private Sub

Dim dbTrade_limit As DAO.Database
Dim dbpath As String
Dim i As Integer

dbpath = "\\Dtcnas-ilsp002\mandatory\Analysts - Working
Files\Carol\Demo\Trade_limit"
Set dbTrade_limit = OpenDatabase(dbpath)

Dim rsCombined_ATE_Mark_to_Market_Client_approval_date As DAO.Recordset
Set rsCombined_ATE_Mark_to_Market_Client_approval_date =
dbTrade_limit.OpenRecordset("Combined_ATE_Mark_to_ Market_Client_approval_date", dbOpenTable)


i = 2
With rsCombined_ATE_Mark_to_Market_Client_approval_date
If Not .BOF Then .MoveFirst
Do While Not .EOF
Cells(i, 1).Value = .Fields(1) 'CompanyNumber in First column
Cells(i, 2).Value = .Fields(0) 'CompanyName in 0 column
Cells(i, 3).Value = .Fields(12) 'TradeLimit Available
.MoveNext
i = i + 1
Loop
End With

rsCombined_ATE_Mark_to_Market_Client_approval_date .Close
dbTrade_limit.Close
Set rsCombined_ATE_Mark_to_Market_Client_approval_date = Nothing
Set dbTrade_limit = Nothing


lastrow = Range("A6000").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="Clients", RefersTo:="=Clients!$A$2:$A$"
& lastrow

Sheets("Pricing Tool").Select

End Sub


--
ca1358


"Bob Phillips" wrote:

Post your question here, getting data from Access is generally pretty
simple. I don't know about Workgroup Security, but someone else might.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"ca1358" wrote in message
...
I need to find a book dedicated to Using ADO. I am importing information
from
Access to Excel using VBA. I am dealing with one database that has just
has
a password and the second one dealing with Workgroup Security with
Security
put on by Security wizard. Could you recommend a book or detail web site
dealing with this. I have done research on the web and found nothing. I
must be putting the wrong key words. John Walkenbach states in his books
there are some out there. Any suggestion would be appreciated!!
--
ca1358

ADO Resource
 

For the password protected database, why didn't you simply supply the
password.

why didn't you supply the password here

Password=;"

change to (assume password is "abcd")

Password=abcd;"


--
Regards,
Tom Ogilvy



"ca1358" wrote in message
...
I made a test database with no password and security and the code worked.
Then I just put the security workgroup and security by using the wizard
and
I get this error: "runtime error 3112, No read permissions.

I made a test database with a password only and I get an error on this
line:
Cnct = Cnct & "Data Source=" & DBFullName & ";" & "User
=mandatory;Password=;"

One of the databases that I have to deal with has a password.
Second one the person used workgroup and security wizard. She did make me
an admin.

I been trying for two weeks to gets this to work. I researched everything
I
can think of on the web and been to the local Banres and Nobles.

Any help would greatly be appreciated or any resource!!


This is my Code I am using for the that has password:
Sub ADO_Demo()
' This demo requires a reference to
' the Microsoft ActiveX Data Objects 2.x Library

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim AS400 As Integer
Dim A1 As Range

' Cells.Clear
' MsgBox "Updating Incentive."

' Database information
DBFullName = ThisWorkbook.Path & "\Volume.mdb"

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";" & "User
Id=mandatory;Password=;"

Connection.Open ConnectionString:=Cnct




' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter
' Src = "SELECT [V05 CONV] ,[V05 GOVT], [Yes incentive] FROM [End
of
year incentive] WHERE AS400 = " & TradeLimit.ComboBox1.Value

Src = "SELECT [V05 CONV] ,[V05 GOVT], [Yes incentive] FROM
[End_of_year_incentive] WHERE AS400 = " & TradeLimit.ComboBox1.Value

.Open source:=Src, ActiveConnection:=Connection

' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Sheet2.Range("a20").Offset(0, Col).Value =
Recordset.Fields(Col).Name
Next

' Write the recordset
Sheet2.Range("a21").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub


This is the code I am using for workgroup and security wizard security.
Private Sub

Dim dbTrade_limit As DAO.Database
Dim dbpath As String
Dim i As Integer

dbpath = "\\Dtcnas-ilsp002\mandatory\Analysts - Working
Files\Carol\Demo\Trade_limit"
Set dbTrade_limit = OpenDatabase(dbpath)

Dim rsCombined_ATE_Mark_to_Market_Client_approval_date As
DAO.Recordset
Set rsCombined_ATE_Mark_to_Market_Client_approval_date =

dbTrade_limit.OpenRecordset("Combined_ATE_Mark_to_ Market_Client_approval_dat
e", dbOpenTable)


i = 2
With rsCombined_ATE_Mark_to_Market_Client_approval_date
If Not .BOF Then .MoveFirst
Do While Not .EOF
Cells(i, 1).Value = .Fields(1) 'CompanyNumber in First column
Cells(i, 2).Value = .Fields(0) 'CompanyName in 0 column
Cells(i, 3).Value = .Fields(12) 'TradeLimit Available
.MoveNext
i = i + 1
Loop
End With

rsCombined_ATE_Mark_to_Market_Client_approval_date .Close
dbTrade_limit.Close
Set rsCombined_ATE_Mark_to_Market_Client_approval_date = Nothing
Set dbTrade_limit = Nothing


lastrow = Range("A6000").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="Clients",
RefersTo:="=Clients!$A$2:$A$"
& lastrow

Sheets("Pricing Tool").Select

End Sub


--
ca1358


"Bob Phillips" wrote:

Post your question here, getting data from Access is generally pretty
simple. I don't know about Workgroup Security, but someone else might.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"ca1358" wrote in message
...
I need to find a book dedicated to Using ADO. I am importing
information
from
Access to Excel using VBA. I am dealing with one database that has
just
has
a password and the second one dealing with Workgroup Security with
Security
put on by Security wizard. Could you recommend a book or detail web
site
dealing with this. I have done research on the web and found nothing.
I
must be putting the wrong key words. John Walkenbach states in his
books
there are some out there. Any suggestion would be appreciated!!
--
ca1358

ADO Resource
 

Here's a few ideas.

Do you get the error on the line you suggest that specifies the connection
string, or on the next line that attempts to open the connection? If it is
really the line you specify, then it's purely a string concatenation
problem.

You might want to try out MZTools which has a very useful ado connection
string generator which I have found useful when first setting up data
sources.

If, it is on the next line, it looks like you need to specify the userid and
password, or change the connectionstring to windows authentication (not sure
if that works with Access - I work mostly with SQL).

If you are experiencing problems opening the recordset after you have the
connection open, try checking permissions against the view or table for the
user account you are running under or connecting under.

(As an aside, and on principle, if you are connecting with a password and
userid in your code, you should probably set up a seperate id just for this
purpose at the db and grant the minimal level of permissions you need to
allow this account to function, not run under the admin account.)

Stephen Bullen's 2002 book has a useful section on ADO.

You can also look here for some useful stuff, particularly for connection
string examples:
http://www.carlprothman.net

And although this is not excel related, I often find myself trawling around
he
http://www.4guysfromrolla.com/

Not sure about the second part of your question I'm afraid.

Robin Hammond
www.enhanceddatasystems.com

"ca1358" wrote in message
...
I made a test database with no password and security and the code worked.
Then I just put the security workgroup and security by using the wizard
and
I get this error: "runtime error 3112, No read permissions.

I made a test database with a password only and I get an error on this
line:
Cnct = Cnct & "Data Source=" & DBFullName & ";" & "User
=mandatory;Password=;"

One of the databases that I have to deal with has a password.
Second one the person used workgroup and security wizard. She did make me
an admin.

I been trying for two weeks to gets this to work. I researched everything
I
can think of on the web and been to the local Banres and Nobles.

Any help would greatly be appreciated or any resource!!


This is my Code I am using for the that has password:
Sub ADO_Demo()
' This demo requires a reference to
' the Microsoft ActiveX Data Objects 2.x Library

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim AS400 As Integer
Dim A1 As Range

' Cells.Clear
' MsgBox "Updating Incentive."

' Database information
DBFullName = ThisWorkbook.Path & "\Volume.mdb"

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";" &
"UserId=mandatory;Password=;"

Connection.Open ConnectionString:=Cnct




' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter
' Src = "SELECT [V05 CONV] ,[V05 GOVT], [Yes incentive] FROM [End
of
year incentive] WHERE AS400 = " & TradeLimit.ComboBox1.Value

Src = "SELECT [V05 CONV] ,[V05 GOVT], [Yes incentive] FROM
[End_of_year_incentive] WHERE AS400 = " & TradeLimit.ComboBox1.Value

.Open source:=Src, ActiveConnection:=Connection

' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Sheet2.Range("a20").Offset(0, Col).Value =
Recordset.Fields(Col).Name
Next

' Write the recordset
Sheet2.Range("a21").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub


This is the code I am using for workgroup and security wizard security.
Private Sub

Dim dbTrade_limit As DAO.Database
Dim dbpath As String
Dim i As Integer

dbpath = "\\Dtcnas-ilsp002\mandatory\Analysts - Working
Files\Carol\Demo\Trade_limit"
Set dbTrade_limit = OpenDatabase(dbpath)

Dim rsCombined_ATE_Mark_to_Market_Client_approval_date As DAO.Recordset
Set rsCombined_ATE_Mark_to_Market_Client_approval_date =
dbTrade_limit.OpenRecordset("Combined_ATE_Mark_to_ Market_Client_approval_date",
dbOpenTable)


i = 2
With rsCombined_ATE_Mark_to_Market_Client_approval_date
If Not .BOF Then .MoveFirst
Do While Not .EOF
Cells(i, 1).Value = .Fields(1) 'CompanyNumber in First column
Cells(i, 2).Value = .Fields(0) 'CompanyName in 0 column
Cells(i, 3).Value = .Fields(12) 'TradeLimit Available
.MoveNext
i = i + 1
Loop
End With

rsCombined_ATE_Mark_to_Market_Client_approval_date .Close
dbTrade_limit.Close
Set rsCombined_ATE_Mark_to_Market_Client_approval_date = Nothing
Set dbTrade_limit = Nothing


lastrow = Range("A6000").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="Clients", RefersTo:="=Clients!$A$2:$A$"
& lastrow

Sheets("Pricing Tool").Select

End Sub


--
ca1358


"Bob Phillips" wrote:

Post your question here, getting data from Access is generally pretty
simple. I don't know about Workgroup Security, but someone else might.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"ca1358" wrote in message
...
I need to find a book dedicated to Using ADO. I am importing
information
from
Access to Excel using VBA. I am dealing with one database that has
just
has
a password and the second one dealing with Workgroup Security with
Security
put on by Security wizard. Could you recommend a book or detail web
site
dealing with this. I have done research on the web and found nothing.
I
must be putting the wrong key words. John Walkenbach states in his
books
there are some out there. Any suggestion would be appreciated!!
--
ca1358