Exporting to Excel using a button & VB code

[Guest]

I have a button with VB code that exports all the data from a table to Excel.
The problem is that some of the fields in the table are linked to other
tables that have autonumbers as primary keys, but I need the text fields and
not the associated numbers to be exported to spreadsheet. Is there any to do
this with VB code or will I need to reset all the primary keys to the text
fields? The code I'm using is listed below. Thanks!

---------------------------------

Private Sub cmdExcel_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qryMain", dbOpenSnapshot)

Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oBook = oApp.Workbooks.Add
Set oSheet = oApp.ActiveSheet

Dim i As Integer
Dim iNumCols As Integer
iNumCols = rs.Fields.Count
For i = 1 To iNumCols
oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next

oSheet.Range("A2").CopyFromRecordset rs

With oSheet.Range("a1").Resize(1, iNumCols)
..Font.Bold = True
..EntireColumn.AutoFit
End With

oApp.Visible = True
oApp.UserControl = True

rs.Close
db.Close

End Sub

 

[John Nurick]

Create a query that joins the various tables and returns the fields
you need, then export the query.

 

[Guest]

Thanks for your response! Sorry, I forgot to mention that the linked fields
in the main table may or may not have data in them, and they need to be
exported regardless if there are data present. If I join the tables in a
query, the query excludes all the rows that do not have data in the
particular linked field. Is there some way around this? I basically need
recreate the entire table in a query even if there are null values in the
linked fields. Thanks ahead of time!

 

[Guest]

Also, I'd like to avoid putting default values in all the empty linked fields
b/c it will make another feature difficult to implement.

 

[Douglas J. Steele]

Use Left Joins between your table and the other tables.

 

[John Nurick]

If they have values (other than Null or perhaps an empty string), they
are by definition not empty!

Default values are assigned when a record is created. If I understand
you right, you'll need to
(a) use update queries to set the values of the relevant fields in the
relevant records in the related tables to Null (or maybe just delete
the records, depending on the circumstances)
(b) in table design view, change the default values of the fields in
question to Null.

 

[saima]

sdfsfsg