[AccessD] Hide datasheet view of table when appending

Darryl Collins Darryl.Collins at coles.com.au
Sat Jun 14 00:50:35 CDT 2008



Hi Jim,

"Is there an advantage to only pushing one column at a time?"

Not that I know of, I used this method as it has proven to be flawless so far and it was fast enough not to investigate other methods.  For example, I use it to load anywhere up to 30,000 plus rows of data with 15 columns in each row, and it usually only take a few seconds.  I am sure some guru could use a different method that is virtually instant using an array or similar.

I also use 'row at a time' if I am changing or removing selected entries before re-writing them.  Really depends on what I need to do I guess.

Always open for advice on this sort of thing.

cheers
Darryl.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Hewson, Jim 
Sent: Friday, 13 June 2008 11:03 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Hide datasheet view of table when appending


Thanks, Darryl - I didn't think of pushing from Excel to Access.
I use something similar to your "air code" below to move through the worksheets.

The end result is a subset of two workbooks; both imported into Access, combined into one table.  Than a subset of fields is then exported out of Access into a CSV file.  This file is used to import the data into a large database maintained by a different company.
My goal is to have Access import the data, manipulate it, and then export it.  There are several steps involved for QC, so reviewing the data at certain steps is needed.

I revisited Lambert's suggestion to use TransferSpreadsheet and I have it almost working now.  

Your code below to push the data from Excel to Access is interesting.
I am trying to pull a larger range into Access - several rows and columns.
Is there an advantage to only pushing one column at a time?

Thanks again.

Jim 
jhewson at nciinc.com 
  

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


Jim,

Being an Excel dude more than an Access Dude, I generally push the data from Excel to Access rather than Use Access to pull it in.  I use code like this based in an Excel VBE module, but I am sure you could mod it so it runs from Access instead without too much bother.

If you have multiple sheets you would want to add in something like

-- air code ---
Dim ws as Excel.Worksheet

For each ws.thisworkbook.worksheets
	do ya thing
Next ws
--- end air code ----

Code I use is below.

*** WATCH FOR WRAP ****

' ================Start Code =========================
Option Private Module
Option Explicit


' Code Version: G4V000.2
' Date Last Updated: 31-Mar-2007

Sub Upload_LTS_Database()

Application.ScreenUpdating = False
Application.StatusBar = "Writing to LTS Archive Database:..."
Application.Calculation = xlCalculationManual

''' gsPATH is dimmed as a public variable

Set gDB = Workspaces(0).OpenDatabase(gsPATH, False, False, dbLangGeneral & ";Pwd=MyPasswordHere")
Set gRS = gDB.OpenRecordset("tbl_LTS_Archive", dbOpenTable)

For Each grCEL In Sheet43.Range("nrLTS_FYP_KEY_ID")
 	Application.StatusBar = "Writing to LTS Archive Database: " & grCEL.Row & " of " & Sheet43.Range _	("nrLTS_FYP_KEY_ID").Rows.Count + 6
    glLR = grCEL.Row
    With gRS
        .AddNew
        .Fields("Key_ID") = Sheet43.Range("B" & glLR).Value
        .Fields("Product") = Sheet43.Range("C" & glLR).Value
        .Fields("Project") = Sheet43.Range("D" & glLR).Value
        .Fields("Phase") = Sheet43.Range("E" & glLR).Value
        .Fields("Owner") = Sheet43.Range("F" & glLR).Value
        .Fields("OwnerData") = Sheet43.Range("G" & glLR).Value
        .Fields("FY") = Sheet43.Range("H" & glLR).Value
        .Fields("FYP") = Sheet43.Range("I" & glLR).Value
        .Fields("Status") = Sheet43.Range("J" & glLR).Value
        .Fields("OpexCapex") = Sheet43.Range("K" & glLR).Value
        .Fields("$AUD") = Sheet43.Range("L" & glLR).Value
        .Fields("MEC_Major") = Sheet43.Range("M" & glLR).Value
        .Fields("Date") = Sheet43.Range("N" & glLR).Value
        .Fields("AdjStatus") = Sheet43.Range("R" & glLR).Value
        .Fields("FYAdj") = Sheet43.Range("S" & glLR).Value
        .Fields("Segment") = Sheet43.Range("T" & glLR).Value
        .Fields("SourceData") = Sheet43.Range("U" & glLR).Value
        .Fields("Programme") = Sheet43.Range("W" & glLR).Value
        .Fields("EffortDays") = Sheet43.Range("X" & glLR).Value
        .Update
    End With
Next grCEL
   
gRS.Close
Set gRS = Nothing
gDB.Close
Set gDB = Nothing

End Sub
' ================End Code =========================

regards
Darryl.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Hewson, Jim 
Sent: Friday, 13 June 2008 7:40 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Hide datasheet view of table when appending


Thanks, I tried that, but I kept getting an error when I defined the range to transfer.
One range might be A4:S4
The next range might be A4:S30
The DoCmd.TransferSpreadsheet gave me an error when I tried to use variables in the range.
How can I make it work?

Jim 
jhewson at nciinc.com 
  

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Thursday, June 12, 2008 3:59 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Hide datasheet view of table when appending

I'd suggest you stop using copy and paste and consider using the
DoCmd.TransferSpreadsheet method.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hewson, Jim 
Sent: Thursday, June 12, 2008 4:48 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Hide datasheet view of table when appending

I can't seem to figure this out....  
Access 2003
I am importing data from an Excel workbook to a table in Access.
All the worksheets will be imported and they have the same format.
The Excel files are created from a download from another system. 
The smallest workbook has 80 worksheets.  One test Excel file has about 2400
worksheets.
 
I am able to work on each worksheet, grab the desired range (could be 1 or
100 rows involved), paste it into Access, and close Excel.
I need to "hide" the table when the data is being appended.
What happens is the table is opened and the data is pasted into it -
everything shows in the database window.
 
How can I hid the table and still paste the data into it?
If I minimize it, I get an error message.
If I hide the table, I get an error message.
 
Any suggestions?
 
Thanks,
 
Jim
 
Jim H. Hewson 
 

############################################################################
####

This email transmission contains information from NCI Information Systems,
Inc. 
that may be considered privileged or confidential and is intended solely for
the named recipient.  If you have received this message in error, please
contact the sender immediately and be aware that the use, copying or
dissemination of this information is prohibited.

############################################################################
####
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
################################################################################

This email transmission contains information from NCI Information Systems, Inc. 
that may be considered privileged or confidential and is intended solely for the 
named recipient.  If you have received this message in error, please contact the 
sender immediately and be aware that the use, copying or dissemination of this 
information is prohibited.

################################################################################

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses.  No warranty is made that this material is free from computer virus or any other defect or error.  Any loss/damage incurred by using this material is not the sender's responsibility.  The sender's entire liability will be limited to resupplying the material.

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
################################################################################

This email transmission contains information from NCI Information Systems, Inc. 
that may be considered privileged or confidential and is intended solely for the 
named recipient.  If you have received this message in error, please contact the 
sender immediately and be aware that the use, copying or dissemination of this 
information is prohibited.

################################################################################

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses.  No warranty is made that this material is free from computer virus or any other defect or error.  Any loss/damage incurred by using this material is not the sender's responsibility.  The sender's entire liability will be limited to resupplying the material.




More information about the AccessD mailing list