Access 2003 to 2007?

[TomUK]

Hi all,

I am new to access 2007 and have brought a database through from 2003. I am having trouble getting some code to work which is run from a button on a form. Is there a major change in the code for 2007 to make the following stop working:


Private Sub ExcelExport()

'Declare a variable named MySheetPath as String.
Dim MySheetPath As String
Dim rs As ADODB.Recordset
Dim o As Object
Dim db As Database
Dim cnn As ADODB.Connection

'Note: You must change the path and filename below
'to an actual Excel .xls file on your own computer.
MySheetPath = "C:\Documents and Settings\TMMY\My Documents"
MySheetPath = MySheetPath + "\My Sheet.xls"

'Set up object variables to refer to Excel and objects.
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

'Open an instance of Excel, open the workbook.
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)

'Make sure everything is visible on the screen.
Xl.Visible = True
XlBook.Windows(1).Visible = True

'Define the topmost sheet in the Workbook as XLSheet.
Set XlSheet = XlBook.Worksheets(1)

'Copy GrandTotal to FromAccess cell in the sheet.
'XlSheet.Range("FromAccess").Locked = False
'Set XlSheet.Range("FromAccess") = chart

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cnn

'Set o = Application.DBEngine
'Set db = o.Workspaces(0).Databases(0)
rs.Open ("Chart")

XlSheet.Range("A2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set o = Nothing

'Boldface the new value (optional).
XlSheet.Range("FromAccess").Font.Bold = True

'Dim i As Integer, j As Integer
'For i = 0 To rs.RecordCount - 1
' For j = 0 To rs.Fields.Count - 1
' With XlSheet
' .Range(
' End With
' Next j
'Next i


'Save the sheet with the new value (optional).
XlBook.Save

'Close the Access form (optional).
DoCmd.Close acForm, "OrderSummaryForm", acSaveNo

'Clean up and end with worksheet visible on the screen.
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
End Sub

Thanks Tom

 

[Rabbie]

An A2003 DB should work under A2007 without problems. Canm you describe what error you are getting and indicate which line is erroring

 

[LPurvis]

Or is it a compile error - due to a missing reference library (in this case for Excel or ADO).

As Rabbie says, providing the exact error is always hugely important.
There are distinct errors for a good reason. They tell us something.

 

[TomUK]

Or is it a compile error - due to a missing reference library (in this case for Excel or ADO).

As Rabbie says, providing the exact error is always hugely important.
There are distinct errors for a good reason. They tell us something.

Yes it is a complile error

"User-defined type not defined"

Error appears on line 4 "Dim rs As ADODB.Recordset"

Thanks,

Tom

 

[JANR]

Error appears on line 4 "Dim rs As ADODB.Recordset"

It's a missing library refrence to ADO, set it under Tools->Refrence.

JR

 

[TomUK]

It's a missing library refrence to ADO, set it under Tools->Refrence.

JR

Thanks JANR,

You will have to excuse my verly limited understanding of VBA. I have looked in tools>Refrence but I cant find any reference to ADO can you give me the exact name I am looking for please?

 

[JANR]

Open your codewindow (ctrl+G) and under Tools -> Refrence. I assume it hasen't moved in Access 2007 if so use help and search for it. I use 2003 so that's were it is. Hope this helps.

JR

 

[TomUK]

I have added DAO but I still get the same error message

 

[JANR]

Not DAO but ADO aka Microsoft ActiveX Data Object.

JR

 

[Banana]

No, you don't need DAO, you need ADO

The full name you want to look for is

Microsoft ActiveX Data Objects 2.8 Library

You have to scroll down to the middle, where all libraries starting with Microsoft are to see that one.

(There's 6.0 library, too, if you want the latest and greatest (?), but 2.8 is probably more common)

 

[TomUK]

Thansk guys that worked.

All I need to do now is link that code to a button on my form.