SQL from Access form to Excel named range

[Duplicity]

I’d like some help with an SQL query from an Access database to an Excel named range.

The scenario is this:

On an Access form that displays the usual suspects of customer information from a table called Clients;

ClientID
Name
Surname
Company Name
Client type – selected from 3 possibilities in a drop down menu
Address1
Address2
Town/County
PostCode
TelNo
FaxNo
Email

I’d like to be able to run a query that will export the contact information for the customer whose records are currently open to view.

Say the database operator is looking at customer number 26, I’d like to be able to click a command button on the Access form that will transpose that customers records to a named range on an Excel worksheet in a workbook that will already be open. It is not important that the command button is on the Access form, it can be on the spreadsheet if need be.

This will only ever be required and used on that customer whose details are on current view.

Effectively, the query will copy and paste the customers contact details based on whichever customers particulars are open on the Customers form in Access.

Does anyone know how this can be achieved ?

I've posted the same question in the "Microsoft: Access Other topics" forum on this site. I thought it was the appropriate forum since I hadn't found this one at that time, so my apologies for cross posting.

 

[Rick39]

This uses a list box as a dataset filter (could put your clients here?)

Private Sub cmdExport_Click()
Dim ctlSource As Control
Dim strItems As String
Dim intCurrentRow As Integer
Dim sql As String
Dim db As Database
Dim rs As Recordset
Dim i, j As Long

Set ctlSource = Forms!frmADHOC_Export!lstFieldNames

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(1, intCurrentRow) & ", "
End If
Next intCurrentRow

' Replace
sql = "SELECT " & Left(strItems, Len(strItems) - 2) & " FROM tblLimitations"
sql = sql & " WHERE " & ship_sql
sql = sql & " ORDER BY tblLimitations.ControlNumber;"
'MsgBox sql

Set db = CurrentDb
Set rs = db.OpenRecordset(sql, DB_OPEN_SNAPSHOT)

CrtExcelBook
On Error Resume Next
rs.MoveLast
rs.MoveFirst
If rs.RecordCount = 0 Then
xlApp.Range("A1&quot = "Recordset is empty"
'GoTo EndSub
End If
For j = 1 To rs.Fields.Count 'Print recordset's fields name
xlApp.ActiveCell.Offset(0, j - 1) = rs.Fields(j - 1).Name
Next j
For i = 1 To rs.RecordCount 'Print records
For j = 1 To rs.Fields.Count
xlApp.ActiveCell.Offset(i, j - 1) = rs.Fields(j - 1)
Next j
rs.MoveNext
Next i
xlApp.ActiveCell.Offset(i, 0) = "Record count: " & rs.RecordCount
xlApp.Application.Visible = True


End Sub

Sub CrtExcelBook()
Set xlApp = CreateObject("Excel.Application&quot
Set xlBook = xlApp.Workbooks.Add
Set xlExcel = xlBook.Worksheets(1)
xlExcel.Range("A1&quot.Select
End Sub

'***********

Hope this helps

Rick

 

[Duplicity]

Wow.

Thanks for taking the time to respond Rick.

I'll take a look at your suggestion and try to apply my non-existent programming skills to it.

Wish me luck (lots and lots of it)

Thanks again.

 

[Duplicity]

I can't get the above suggested method to work (my lack of knowledge in that area - I have no doubt about that).

An Access append query I managed to come up with is as follows:

INSERT INTO TransferTable ( Name, Surname, Company, Address1, Address2, [Town/City], County, PostCode, Phone, Fax, Email, [Client type] )
SELECT [Clients].[Name], [Clients].[Surname], [Clients].[Company], [Clients].[Address 1], [Clients].[Address 2], [Clients].[Town/City], [Clients].[County], [Clients].[Post Code], [Clients].[PhoneNumber], [Clients].[FaxNumber], [Clients].[Email Address], [Clients].[ClientType]
FROM Clients
WHERE [Name]=[Forms]![Client Form]!Name;

It works almost perfectly, with one exception; the Query drops to the row below its previous entry each time I run it. No doubt there will be much wise wagging of heads at that, and I am assuming that the job of an append query is to continue where it left off last time it ran. What I need it to do though, is overwrite the same cells every time it is run.

The target sheet of a workbook (called TransferTable) is linked to the database and another sheet in the same workbook manipulates the information in one row of the TransferTable sheet. Obviously, if the append query goes below that row, there is nothing for the other sheet to work with.

Is there any way in which I can get the above query to either target specific cells on the link table or delete its original entries and replace them with the information it transfers each time the query is run ?

Help !

 

[Rick39]

You can clear the contents of the sheet before populating it using:
Cells.Select
Selection.ClearContents