Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How would I change this to ADO?

Posted on 2006-05-08
5
Medium Priority
?
171 Views
Last Modified: 2010-04-07
How would I change this to ADO?

Sub OpenDB()
 Dim db As New ADODB.Database
 Dim ws As ADODB.WorkSpace
 Dim rst As ADODB.Recordset
 Dim i As Integer
 
 Cells.Clear
 MsgBox "Update TradeLimit"
 
 
 Set ws = DBEngine.WorkSpaces(0)
 Set db = ws.OpenDatabase _
 ("\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Carol\Demo\volume.mdb", _
 False, False, "MS Access;PWD=mandatory")
 
 ''''''''''''''''''''''''''''''''''''''''''
 
 Set rst = db.OpenRecordset("Maintenance Table", dbOpenDynaset)
    i = 2
     
      With rst
        .OpenRecordset
         '/ Add Field Names
         Cells(1, 1).Value = .Fields(0).Name
         Cells(1, 2).Value = .Fields(1).Name
         Cells(1, 3).Value = .Fields(18).Name
         Cells(1, 4).Value = .Fields(11).Name
         Cells(1, 5).Value = .Fields(12).Name
         Cells(1, 6).Value = .Fields(13).Name
         Cells(1, 7).Value = .Fields(14).Name
         If Not .BOF Then .MoveFirst
    End With
    Cells(2, 1).CopyFromRecordset rst
   
   
 rst.Close
 db.Close


Range("A2:s400").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), Order2:=xlAscending




End Sub
0
Comment
Question by:ca1358
  • 3
5 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 16634081
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16634165
Sub OpenDB()
 Dim db As New ADODB.Connection
 Dim rst As ADODB.Recordset
 Dim i As Integer
 
 Cells.Clear
 MsgBox "Update TradeLimit"
 
 
 Set db = new adodb.connection
 db.open "Provider=Jet.OLEDB;Database=""\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Carol\Demo\volume.mdb"";"
 
 
 Set rst = db.OpenRecordset("select * from Maintenance Table")
    i = 2
     
    With rst
         '/ Add Field Names
         Cells(1, 1).Value = .Fields(0).Name
         Cells(1, 2).Value = .Fields(1).Name
         Cells(1, 3).Value = .Fields(18).Name
         Cells(1, 4).Value = .Fields(11).Name
         Cells(1, 5).Value = .Fields(12).Name
         Cells(1, 6).Value = .Fields(13).Name
         Cells(1, 7).Value = .Fields(14).Name

         If Not .BOF Then .MoveFirst
    End With
    Cells(2, 1).CopyFromRecordset rst
 
   
   rst.Close
   db.Close

  Range("A2:s400").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), Order2:=xlAscending
End Sub
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 2000 total points
ID: 16634211
Sub OpenDB()
Dim db As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim i As Integer
   Cells.Clear
   MsgBox "Update TradeLimit"
   Set db = new ADODB.Connection
   Set rst = New ADODB.Recordset
    db.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Carol\Demo\volume.mdb;"

    rst.Open "select * from Maintenance Table", db, adOpenStatic, adLockOptimistic

    i = 2
    With rst
         '/ Add Field Names
         Cells(1, 1).Value = .Fields(0).Name
         Cells(1, 2).Value = .Fields(1).Name
         Cells(1, 3).Value = .Fields(18).Name
         Cells(1, 4).Value = .Fields(11).Name
         Cells(1, 5).Value = .Fields(12).Name
         Cells(1, 6).Value = .Fields(13).Name
         Cells(1, 7).Value = .Fields(14).Name

         If Not .BOF Then .MoveFirst
    End With
    Cells(2, 1).CopyFromRecordset rst
    rst.Close
    db.Close
    Range("A2:s400").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), Order2:=xlAscending
End Sub

0
 

Author Comment

by:ca1358
ID: 16634267
Thanks  and thanks for the tutorial, too.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 16634286
Your welcome and thanks for the grade,

Leon
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month11 days, 6 hours left to enroll

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question