[AccessD] Hide datasheet view of table when appending

>>>Answers in-line

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl
Collins
Sent: Monday, June 16, 2008 12:19 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Hide datasheet view of table when appending



Drew,

Could I please bug you for some advice with this?

>>>Of course, and it's not bugging me... ;)

I have tested your code with a range of data in a workbook that starts
at Range A1 (with headers) and it worked great.  For most of the code I
can what is happening but I am not sure how it can be tweaked in a
couple of places.

' This line seems to get the names of all the sheets in the workbook.
Set rs = XLcnn.OpenSchema(adSchemaTables)

>>>Yes, you can use this in ADO to get the list of all the tables in a
database.  I use this method because most processes where I am reading
data from Excel, I don't necessarily know what the sheet name is.

'This line goes to the first sheet in the workbook (or more accurately
the schematables I would think)
rs.MoveFirst

>>>Actually, to be more accurate the OpenSchema(adSchemaTables) returned
a recordset with the following fields:

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE

>>>so rs.movefirst is just going to the first record (which, when
dealing with an excel spreadsheet, would be the record representing the
info for the first sheet)

' This line seems to define the data set to be used - not sure if
"TABLE_NAME" can be anything or not? Actually not sure how this works at
all.
strSheetName = rs.Fields("TABLE_NAME").Value

>>>As I just mentioned, "TABLE_NAME" is a field in the adSchemaTables
Schema.  All this line is doing is storing the name of the sheet I want
to open.

' No need to explain - however what if the sheet you want is not the
first sheet or you know exactly which sheet you do want?
rs.MoveFirst

>>>Then you can completely skip the adschema stuff, and just open the
sheet or range (sheets end with $, where ranges do not)

I was also wondering if it was possible to use Excel's Named ranges
instead of sheets.  All of the dataset I use are in named ranges that
automatically adjust shape as needed.  Ideally I would like something
like:

Sheets("MyExcelSheet").Range("MyNamedRange") =
rs.Fields("TABLE_NAME").Value

>>>Yes, you can just open the range without using the schema, the same
'sample code' I posted with opening a known range would be like this:

Function ImportExcelData()
Dim XLcnn As ADODB.Connection
Dim XLrs As ADODB.Recordset
Dim rs As ADODB.Recordset
Dim strSheetName As String
Dim i As Long
Set XLcnn = New ADODB.Connection
XLcnn.Provider = "Microsoft.Jet.OLEDB.4.0"
XLcnn.Properties("Extended Properties") = "Excel 8.0;HDR=No"
XLcnn.Open "E:\tblRequests.xls"
Set rs = New ADODB.Recordset
rs.Open "tblRequests", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTableDirect
Set XLrs = New ADODB.Recordset
XLrs.Open "NameOfYourDataRange", XLcnn, adOpenKeyset, adLockReadOnly,
adCmdTableDirect
If XLrs.EOF = False Then XLrs.MoveFirst
Do Until XLrs.EOF = True
    rs.AddNew
    For i = 0 To XLrs.Fields.Count - 1
        If Not IsNull(XLrs.Fields(i).Value) Then
            rs.Fields(XLrs.Fields(i).Name).Value = XLrs.Fields(i)
        End If
    Next i
    rs.Update
    XLrs.MoveNext
Loop
rs.Close
Set rs = Nothing
XLrs.Close
Set XLrs = Nothing
XLcnn.Close
Set XLcnn = Nothing
MsgBox "Done"
End Function

>>>Where you would need to replace "NameOfYourDataRange" with the name
of the range you want to use (and also the E:\tblRequests.xls with the
name of the .xls file you want to use).


That would be very cool indeed.  - heh, actually I might try and play
with that and let you know what happens.

cheers
Darryl.




-----Original Message-----
From: Darryl Collins 
Sent: Monday, 16 June 2008 10:45 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Hide datasheet view of table when appending



Drew,

Thanks heaps for this info, I will definately have a play with this
technique later this week.

Great Stuff.

Cheers
Darryl
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.