updating from DAO 3.6 object library

[baitmaster]

I have created a marvelous new system that uses a single Access database table (2003: .mdb format) to hold data "behind the scenes", with an Excel user interface. The interface is set up using DAO object library 3.6

some example code:

Dim dB As DAO.Database: Set dB = OpenDatabase(strDBname)
Dim rsTimesheet As DAO.Recordset: Set rsTimesheet = dB.OpenRecordset(strTBLtimesheets, dbOpenTable)

there is then heaps of SQL etc, and other code to query and update the data table

The system works perfectly (sort of..!), and is only days away from launch. However, I have just found out that the system will actually be used from Windows 7 (64-bit) PCs running Office 2010. The DAO object library does not exist in this version, and is apparently unavailable for the 64 bit system

Please suggest, what is the best way to reconfigure this system? I can rewrite the database into a different format, but dont want to rewrite all my SQL etc that is written into the VBA code... and I have no time to do this

Thanks once again for any help

 

[baitmaster]

a rather shameless and slightly anxious >Bump<

 

[RoryA]

You'll need to switch to ADO. The SQL should be unchanged though you will need to use an ADODB connection rather than opening a database.

Edit: rereading MS article, although there is no 64bit version of DAO, 32bit should still run: see here and MSKB here. I would still recommend switching to ADO as I believe DAO is regarded by MS as obsolete...

 

[Ruddles]

...the system will actually be used from Windows 7 (64-bit) PCs running Office 2010. The DAO object library does not exist in this version, and is apparently unavailable for the 64 bit system...

Are you sure? Googling excel 2010 dao suggests otherwise (unless I have misunderstood you).

I think the Jet database engine might be unavailable in 64-bit 2010 (replaced by the Ace engine) but DAO should be okay.

Have you tried it at all?

(Pondering a Blackbird...)

 

[baitmaster]

thanks all

Ruddles - Hilarious table last week btw, I wonder who else got the gag?!
- no, Im not sure about anything on this subject! I know v little about Access, Ive simply found that I can use it to hold the data that Excel needs to process, and that the SQL link is exceptionally quick for what I need

Both - The configuration I am using is something for which I was provided some examples to copy, and found them to work ok, and that I came to understand. Now these are being removed, I dont know how to update them to a different system, and have no examples to learn from or time in which to do it

I am happy to change from DAO to ADO, from database to ADODB etc, but I dont know the other stuff that Id need to go with it - for example, is it as simple as changing my OP code to ADO.recordset from DAO.recordset? Do I need to consider other things like- changes to my SQL, what Libraries I might need, etc?! A brief overview would be very welcome right now

Essentially, how much work / time would be involved (if its more than 3 days from scratch, Im screwed!), or do I just go tell IT in no uncertain terms, we arent using XL10 and I dont care who says otherwise?!

Good old MS, moving goalposts again...

Thanks again chaps

 

[RoryA]

You need to add a reference to the MS ActiveX Data Objects library then use code like this:

Sub GetAccessData()
   ' Sample demonstrating how to return a recordset from an Access db
   ' requires a reference to the Microsoft ActiveX Data Objects Library.
   
   Dim cnn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset
   Dim strPathToDB As String, strFormula As String, i As Long
   Dim wks As Worksheet
   Dim lngNextNum As Long, lngRow As Long, lngCol As Long
   Dim varData
   
   
   ' output to activesheet
   Set wks = ActiveSheet
   
   ' Path to database
   strPathToDB = "h:\test\db1.mdb"
   
   Set cnn = New ADODB.Connection
   ' open connection to database
   With cnn
      .CursorLocation = adUseServer
      .ConnectionTimeout = 500
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=" & strPathToDB & ";"
      .Open
      .CommandTimeout = 500
   End With
   ' SQL query string - change to suit
   strQuery = "SELECT [Field1], [Field2] as BLah;"
   ' create new recordset
   Set rst = New ADODB.Recordset

   ' open recordset using query string and connection
   With rst
      .CursorLocation = adUseServer
      .Open strQuery, cnn, adOpenStatic, adLockPessimistic, adCmdText
      rst.Filter = "id = 1"
      ' check for records returned
      If Not (.EOF And .BOF) Then
         'Populate field names
         For i = 1 To .Fields.Count
            wks.Cells(1, i) = .Fields(i - 1).Name
         Next i
         ' Copy data from A2
         wks.Cells(2, 1).CopyFromRecordset rst
      End If
      .Close
   End With

   ' clean up
   Set rst = Nothing
   cnn.Close
   Set cnn = Nothing
End Sub

 

[RoryA]

Good old MS, moving goalposts again...

To be fair, they have been saying for about 10 years that DAO was dead.

 

[baitmaster]

To be fair, they have been saying for about 10 years that DAO was dead.

Well yes, but I am behind the times on this subject! I only just learnt about DAO... As soon as one catches up they seem to go off in another tangent... still, got to keep doing new stuff so people buy it I suppose!

As for your code example, thanks, that looks exactly what I need, and it doesn't look too different to what I already have... hopefully I can update my system to work with this instead, within my extremely tight timescales. Time to get on the case...

Lost count of the number of times you have helped me now, yet again, thanks

 

[RoryA]

Well yes, but I am behind the times on this subject! I only just learnt about DAO... As soon as one catches up they seem to go off in another tangent... still, got to keep doing new stuff so people buy it I suppose!

If they didn't we'd be accusing them of stagnation and being behind the times.
Plus, imagine how dull life would be if stuff just kept working from version to version...

 

[baitmaster]

I'd be out of a job...!