swtich DAO to ADO, how to get a record into Excel

[Guest]

I have 2 questions about this function. I changed it from DAO.DAtabase,
DAO.Recordset to ADO.Database and ADO.Recordset. The reason was I am on a
borrowed computer and DAO is missing from references. I just have to finish
writing this program and then buy my own computer. I tried adding DAO by
installing the MDAC and it didn't seem to install. My question is:

1. Will the function work if I switch the ref from DAO to ADO? Is it
possible to test it using ADO and then change it back to DAO for when I send
it to someone? I'm assuming it should be DAO and the person I have to send
it to will most likely have DAO.

Other question:
2. How do I create the SQL query listed in this program? I have a form.
In my sub procedure attached to the input form I have an AfterUpdate event.
The sub procedure calls this function. What I want is to add each record to
an excel table. Do I write a SQL update query or do I eliminate the SQL
query? I just need the last record added after every update to the table via
the form. Is there another way to get the data from Access from the form
without the SQL?

Thanks very much.




Private Declare Function CreateXLFile Lib "Kernel 32" (ByVal str_Filename As
String)
Dim appExcel As excel.Application
Dim wbk As excel.Workbook
Dim wks As excel.Worksheet

Dim dbs As ADO.Database
Dim rst As ADO.Recordset
Dim sSQL As String
Set appExcel = excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open("emp.xls")

Set wks = appExcel.Worksheets(1)
wks.Activate


sSQL = "<sql query to retrieve data">
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL)
For i = 1 To 27
Range("a8").Offset(0, i - 1).Value = rst.Fields(i - 1).Name
Next i
Range("a9").CopyFromRecordset rst
Set dbs = Nothing
End Function



---------------sub procedure--------------------------



Dim lngFileHandle As Long
Dim lngLastError As Long
Const STR_DIRECTORY_PATH = "c:\Test"
Const str_Filename = "emp.xls"
Const OPEN_EXISTING = 3&


Select Case Check_Dir_File
Case 1
'Check if directory exists

If Dir(STR_DIRECTORY_PATH) = "" Then
MkDir STR_DIRECTORY_PATH
End If
Case 2
'Check file exists
If Dir(STR_DIRECTORY_PATH & str_Filename = "") Then
lngFileHandle = CreateXLFile(strFileName)

End If

End Select

End Sub

 

[Douglas J. Steele]

It's not possible for Access to be installed on the computer and not have
the DAO library installed. Are you sure you looked for the correct reference
(Microsoft DAO 3.6 Object Library)? Worst case would be that you need to go
to \Program Files\Common Files\Microsoft Shared\DAO and reregister
dao360.dll. If memory serves, MDAC hasn't contained DAO since MDAC 2.1.

ADO and DAO are not identical. There's no Database object in the ADO object
model. Instead, there's a Connection object.

Your SQL cannot be an Update query: action queries (INSERT INTO, UPDATE,
DELETE, SELECT ... INTO) do not generate recordsets. However, from your
description, you don't need a recordset. All you have to do is put the code
in the form's AfterUpdate event and refer to the various controls on the
form.

 

[Guest]

very good thanks .

It's not possible for Access to be installed on the computer and not have
the DAO library installed. Are you sure you looked for the correct reference
(Microsoft DAO 3.6 Object Library)? Worst case would be that you need to go
to \Program Files\Common Files\Microsoft Shared\DAO and reregister
dao360.dll. If memory serves, MDAC hasn't contained DAO since MDAC 2.1.

ADO and DAO are not identical. There's no Database object in the ADO object
model. Instead, there's a Connection object.

Your SQL cannot be an Update query: action queries (INSERT INTO, UPDATE,
DELETE, SELECT ... INTO) do not generate recordsets. However, from your
description, you don't need a recordset. All you have to do is put the code
in the form's AfterUpdate event and refer to the various controls on the
form.