Importing from excel (1 Viewer)

Danny

Registered User.
Local time
Today, 06:55
Joined
Jul 31, 2002
Messages
140
Greetings,

We're currently running office/access 365 the db is deployed in split (be/fe) environment. We have a provider table w/ almost 80k records. The way the db is designed (by someone else), or supposed to work is as follows:
Whenever users create a new case, a search window appears asking them to enter (provider name, provider number etc.) to see if the provider already exists, if the provider exists, then they will select it and update the remaining information. If the provider doesn't exist, then users will be prompted that "can not find provider... would you like to create a new provider?" then users click Ok to create a new record.

I'm not sure this is due to the fact that we have over 65k records, to search thru, but this process (the search) has been too slow i.e. spins forever, freezes up etc.
Now, I'm tasked to update the current provider table with the latest list that is saved in excel file.

1. If I move on with the update, is there a better approach for the workflow?
I learned access can handle more than 65k, but what to look is how big the db is etc.
2. Since the db is split, I checked properties of the back end (tables) and noticed 780MB, is this the correct way to identify how big the db is?


TIA
Regards,
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:55
Joined
Oct 29, 2018
Messages
21,357
Hi. Are we talking about an Access database for the backend? If so, the file size of the database is usually the concern, not the number of records, because there is a limit on file size, but not really on record numbers. To update the table with data from Excel, are you talking about replacing the whole lot, or just adding new ones?
 

plog

Banishment Pending
Local time
Today, 05:55
Joined
May 11, 2011
Messages
11,611
...a search window appears asking them to enter (provider name, provider number etc.) to see if the provider already exists... this process (the search) has been too slow i.e. spins forever, freezes up etc.

1. There are 2 hows here. The first how--how the search works for the user--is how I would make this work. The second how--how the search is performed via code--doesn't like its correct. I'm not really how that how works, but it shouldn't take any time for a form to filter to the correct results on a table of 65k results. I would dig into the nuts and bolts of how the coding works on that process and make it work more efficiently.

2. Yes, right click on the database and see how big the file is.
 

Danny

Registered User.
Local time
Today, 06:55
Joined
Jul 31, 2002
Messages
140
Thank you both,
theDBguy,
Are we talking about an Access database for the backend?
I wanted to know how to find out the file size of the database.
As for updating the table from excel, the plan is adding new ones. But, looking at the list, we were given existing providers that are already stored in the database along with new providers. My, question is, will the existing providers be imported, or access recognizes that records already exist and only new providers will be imported? (which is what the plan is)

Plog, once I updated the table, I’ll look into the code and try to understand the logic. Maybe, post sample code to get some feedback to enhance it.

TIA,
Regards,
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:55
Joined
Feb 28, 2001
Messages
26,996
The next question is your update from an Excel file. The way you describe this is ambigous. Are you looking to replace or merely to supplement the list? And if the answer is "supplement" then do you know if the Excel list potentially contains data that overlaps with your current list.

Another question has to do with the size of the database file. There is no doubt that a 780 KB database can exist and would be quite reasonable. But do you know how to compact & repair a database? If so, what is the DB size after a completed Compact & Repair? Because for any database that can update or delete older information, there is a problem called "bloat" that makes a database look larger than it really is. We can discuss all of those topics and more, but we need to assess just where you are. The questions I asked would help in that assessment.

EDIT: Our posts crossed. You posted #4 just before I posted my questions and comments.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:55
Joined
Feb 28, 2001
Messages
26,996
To answer one of your questions:

Access will not automatically exclude duplicate data but can be given instructions that would do have that effect. There are several ways to do this.

As far as an "accurate" size of the database: You need to do several things in sequence to protect yourself.

1. Before deciding what else to do, you should set aside a folder where you can work on the files. COPY each file to this secondary folder. Work on the files there. Leave the "production" copies alone (for now).

2. For both FE and BE files, one at a time: Open up the file. On the ribbon, select the File tab. You will see "Compact & Repair". Click that. It should not take very long. Close each file. Right after the files have been Compacted & Repaired (C&R), they are at their smallest possible size and at that point you can get a pretty accurate file size.

3. If you are having speed issues, we will need you to be able to evaluate the nature of your tables. I do not wish to make assumptions as to your abilities, so I will give you a couple of topics to look up online. IF you already understand these topics, great. If not, there are some things to be learned that will be helpful.

Look up these topics: "Database Normalization" and "Table Index". If you use the Search feature in this forum, you can omit "Database" from the normalization search because this IS a database forum.
 

Danny

Registered User.
Local time
Today, 06:55
Joined
Jul 31, 2002
Messages
140
Thanks for your feedback.
I. I already set up a test environment to perform and test this task.
2. Every morning (before end users access the DB) I do compact and repair and backup the database. After I completed these tasks, I right clicked the backend, go to properties to learn the size (780 MB).
3. Speed/database normalization you stated might be the problem here. But, as you suggested, I’ll search and learn more about these topics and post back if I’ve any questions.

TIA

Regards,
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:55
Joined
Feb 28, 2001
Messages
26,996
When you learn about indexes, what you need to look for are TWO possible indexes.

Every table probably should have a prime key, though there are cases where that is not a requirement, just a suggestion. It always pays to know your prime key. And that PK is always your first index.

However, you are doing a search. Any field on which you would do a search should have a declared index. If you have 80K records and are searching a non-indexed table, you have an expectation to search 40K records (based on statistics). If you have 80K records in an indexed table, that same search would have to touch LOGbase2(80K) which is 17 records. Therefore, if you can identify the most commonly searched fields, you can establish an index on each of those fields (if they are not already indexed). I would bet on a pretty good performance boost.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:55
Joined
Feb 28, 2001
Messages
26,996
Thinking about it some more, "normalization" in a simple one-field search will NOT usually be the problem. This is almost always indexing. But what I really wanted to discuss was that Import operation you mentioned.

Here is the way I would approach your import. You suggested that there is some kind of ID number or some unique name that identifies each unique record in your table that you wish to update. What I would do is...

1. Import the new data into a temporary table where you can "condition" it - perhaps you might call this "grooming" or "cleaning" or whatever you do to assure that it is in the right format.

2. Be sure that there is an index on whatever you use as the unique ID number for that record in the main table. Earlier I believe you called it a provider number?

3. For sake of argument, let's call the main supplier table S and the temporary table T. The SQL to do the selective update MIGHT look like this.

Code:
INSERT INTO S (list of fields to be inserted) SELECT list of fields to be copied FROM T WHERE T.PROVIDERID NOT IN ( SELECT S.PROVIDERID FROM S );

The list of field names to be inserted will correspond to the list of fields to be copied, field for field, so the lists must align correctly. That WHERE clause is how you tell Access to not take records from the temp table if they are already in the main table. See the second example in this link:


Once you do this, you can delete the temp table. Interestingly enough, you COULD create the temp table in the FE file and thus minimize the bloat associated with deleting a table in your BE file. And if you do this through a private copy of the FE, you can delete the temp table from that FE and do a C&R, again of the private FE, that nobody else will see. I.e. no disruption to other users.
 

Danny

Registered User.
Local time
Today, 06:55
Joined
Jul 31, 2002
Messages
140
Thanks for the suggestions. I'm certain the table has PK. As for index,
Any field on which you would do a search should have a declared index.
I checked the table in design view and attached is what I see. Is this what you were referring about PK & index?

TIA

Regards,
Index.png
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:55
Joined
Feb 28, 2001
Messages
26,996
When looking at a table in DESIGN view, in the ribbon there should be an Indexes icon that if clicked would list the fields that have indexes. It should be just under the word "Tools" that is part of "Database Tools." At least, that is where it is on my copy of Access.

What you showed does, however, indicate a request for a non-unique index. That index should help with speed. If it does not then there are some things that a query could do that would negate their ability to use an index.

For instance, if you had "provider name" (PROV_NAME) searches and the query contained "... WHERE PROV_NAME LIKE '*" & some string & "*' AND ..." (essentially that is a "contains" comparison), that would kill the search's efficiency. In this kind of search, the "some string" would probably come from some control on the form, Me!textboxname for example. Though it could equally come from some other source.
 

strive4peace

AWF VIP
Local time
Today, 05:55
Joined
Apr 3, 2020
Messages
1,003
hi Danny,

adding on to the great advice you've already been given ...

especially when using table indexes, it is important to set the SIZE property of the fields appropriately, not leave them at the default size of 255! This is particularly relevant for any fields that have an index
 

Danny

Registered User.
Local time
Today, 06:55
Joined
Jul 31, 2002
Messages
140
Thank you both for your feedback.
Attached is the screenshot to show the index and field size for provider table in design view. Crystal was this (2nd screenshot) the field size you were referring?

The-Doc_Man
That index should help with speed. If it does not then there are some things that a query could do that would negate their ability to use an index.
As you stated there are some other things that I need to investigate as the search takes forever. It's so frustrating.

TIA
Regards,
Index & field size.png
 

strive4peace

AWF VIP
Local time
Today, 05:55
Joined
Apr 3, 2020
Messages
1,003
hi Danny,

You're welcome.

yes, that is the Size I mean. Chances are 100 is too much. If these are Canadian provinces, the longest name (in English) is 25 (Newfoundland and Labrador)

I also notice the format is set to "@". This should be deleted. It gets filled when you create a table by importing data.

Also, when you import data, Unicode Compression is set to No. This should be changed to Yes (in most cases). It only applies to Short Text and Long Text.

What is the Data Type and Size of the PROV# and LOC fields?

It is a good idea to not use special characters such as # in field names. If the data is a numeric data type, I abbreviate # with Nbr. If the data type is text, I use Num. This lets me know when I see the field name what the data type is, which is important to keep track of in Access.

> "so frustrating"

I know how you feel! Hopefully we can help alleviate that for you.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:55
Joined
Feb 28, 2001
Messages
26,996
I see Crystal has stepped in while I was out. Sorry I didn't return sooner, but let's be honest here... the wife is the boss. When she needs my help in something, the forum is 2nd place.

When you talk about slow searches, there can be only a few likely causes.

1. Total size - but you've said your table was no more than about 80k records, and I've worked with tables 10 times that size without major issues so I will GUESS that size isn't necessarily an issue in and of itself. Colin has reported success with tables pushing 1Mil records. Size as a number of records MIGHT contribute for relation scans. (A "relation scan" is what you do when the index isn't being used.)

2. No usable index - but you've shown that the index exists on some things you mentioned as being likely for your search. So unless there is a field you are searching that ISN'T indexed, then this isn't your problem either. But having an index, as mentioned before, isn't enough.

3. Query voids effect of index - here, we have beaten around the bush. But we still haven't examined exactly how the search works. This could require a little more effort. We need to understand exactly how the search gets implemented. Can you enlighten us there? If there is a query, can you copy it and show it to us? And by the way, I mentioned a search that put a wildcard in front of the search string. But a UNION query will also whack your index usage pretty badly. A function of an indexed field in the query would also do no particular good.

4. In combination with #3, how long is a single record? You have to add up the estimated character counts for every string and the number size for every number. The longer the record, the worse a non-indexed search becomes, because Access has to pull in the entire table if it can't do an index scan to find what it wants. If that happens, the size of one record governs how fast that so-called "relation scan" takes because Access cannot read more data than its buffer size. Longer records = fewer records per disk cluster (a.k.a. storage allocation unit).

5. Nature of the connection between FE and BE. If the network is slow, anything you do over it will also be slow. If you are doing a relation scan across a slow network, you are in the worst of all possible worlds.
 

Micron

AWF VIP
Local time
Today, 06:55
Joined
Oct 20, 2018
Messages
3,476
From what I've read, Access doesn't reserve space beyond what is required to store actual values for short text. Therefore, a field with a limit of 100 characters and a value of 10 characters takes up the same space as one with a limit of 255 and a value of 10 characters. Then the only reason for worrying about short text character limit would be to control data input; e.g. limit to 4 for department codes so that no more than 4 can be entered. I don't know if this is true for long text or not. It is not the case for numbers.
 

strive4peace

AWF VIP
Local time
Today, 05:55
Joined
Apr 3, 2020
Messages
1,003
Micron, Size matters in an index (entries will take that space even if it isn't being used). It also makes a difference in performance when joining fields. One application I worked on had tables joined on text fields and one way I speeded it up was by reducing field Size even though the contents didn't change. It isn't supposed to matter, but it does.
 
Last edited:

Danny

Registered User.
Local time
Today, 06:55
Joined
Jul 31, 2002
Messages
140
Thank you everyone for your input.

The_Doc_Man,

The wife is the boss…the same here.
Everything you outlined make a lot of sense. In your summary #3. and in the previous post, you stated “wild card in front of the search string etc. as one of the causes for slowness.

For instance, when looking at the excel file with provider information, I noticed a lot of data entry errors (screenshot with leading characters).

Just inquiring about how it was done in the past, I was told no one cleans up the data we just update the table from excel as we get them.

So, if a user is searching for a provider name Eugene (first provider in screenshot) they do not get the result as desired as it stored as -Eugene. As you suspected, somewhere there might be a wildcard in front of a search etc.

I’ve not yet updated the provider table. I suggested to the management that cleaning up the data should be the priority. Also, I'm working on formatting the excel file the same way as the existing table etc.

As you suggested, I will attempt to provide the code to show how the search works. Usually when a user clicks new record button on the form, that’s when the search box appears to allow users to enter provider name, provider number to search.

Which code should I provide? On open event?

TIA
ProviderName.png

Regards,
 

Attachments

  • ProviderName.png
    ProviderName.png
    7.3 KB · Views: 90

strive4peace

AWF VIP
Local time
Today, 05:55
Joined
Apr 3, 2020
Messages
1,003
hi Danny,

first, please would you document your data structure?

Create a new general module. Then paste in this code:

Code:
Option Explicit

'*************** CODE BEGIN
' Crystal (strive4peace)
' based on code written by Duane Hookom
'
'~~~~~~~~~~~~~~~~~~
      'NEEDS reference to Microsoft DAO Library
      'or
      'Microsoft Office ##.0 Access Database Engine Object Library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'        for instance -->   RunShowFieldsForAllTables
'--- press F5 to run

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub RunShowFieldsForTable()
   'edit this line for the tablename you wish to document
   ShowFields "Your tablename"
End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub RunShowFieldsForAllTables()

   'click HERE
   'press F5 to Run!

   Dim i As Integer _
      , mTablename As String
   For i = 0 To CurrentDb.TableDefs.Count - 1
      mTablename = CurrentDb.TableDefs(i).Name
      If Left(mTablename, 4) <> "Msys" Then
        Debug.Print 'blank line
        ShowFields mTablename
      End If
   Next i
End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
    'by Duane Hookom
    'modified by Crystal

    Dim Fld As DAO.Field
    Dim tbl As DAO.TableDef
    Dim db As DAO.Database

    Set db = CurrentDb
    Set tbl = db.TableDefs(pstrTable)

    Debug.Print tbl.Name
    Debug.Print "=========================="

    For Each Fld In tbl.Fields
      'modified by Crystal
        Debug.Print Fld.OrdinalPosition & "  " & Fld.Name _
         & ", " & Fld.Type & " (" & GetDataType(Fld.Type, True) & ")" _
         & ", " & Fld.Size;
        If (Fld.Attributes And 16) = 16 Then
           Debug.Print " (Auto)"
        Else
           Debug.Print
        End If
    Next

    'release object variables
    Set Fld = Nothing
    Set tbl = Nothing
    Set db = Nothing

End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~~~~~~~~~~~~~~~~~~~~~~~~ GetDataType

Function GetDataType(ByVal pDataTypN As Long _
   , Optional pBooShort As Boolean = False _
   ) As String

'100310

   ' Crystal (strive4peace)

   GetDataType = ""
   On Error Resume Next

   Switch
   Select Case Nz(pDataTypN)
      Case 1: GetDataType = IIf(pBooShort, "YN", "Boolean")
      Case 2: GetDataType = IIf(pBooShort, "Byt", "Byte")
      Case 3: GetDataType = IIf(pBooShort, "Int", "Integer")
      Case 4: GetDataType = IIf(pBooShort, "Lng", "Long")
      Case 5: GetDataType = IIf(pBooShort, "Cur", "Currency")
      Case 6: GetDataType = IIf(pBooShort, "Sgl", "Single")
      Case 7: GetDataType = IIf(pBooShort, "Dbl", "Double")
      Case 8: GetDataType = IIf(pBooShort, "Date", "DateTime")
      Case 10: GetDataType = IIf(pBooShort, "Txt", "Text")
      Case 12: GetDataType = IIf(pBooShort, "Mem", "Memo")
  
      Case 9: GetDataType = IIf(pBooShort, "Bin", "Binary")
      Case 11: GetDataType = IIf(pBooShort, "Ole", "Ole BinBMP")
  
      Case 15: GetDataType = IIf(pBooShort, "Guid", "GUID")
      Case 16: GetDataType = IIf(pBooShort, "BigInt", "Big Integer")
      Case 17: GetDataType = IIf(pBooShort, "BinVar", "Binary Variable")

      Case 18: GetDataType = IIf(pBooShort, "TxtFix", "Fixed Text")
  
      Case 19: GetDataType = IIf(pBooShort, "oNum", "Numeric odbc")
      Case 20: GetDataType = IIf(pBooShort, "oDec", "Decimal odbc")
      Case 21: GetDataType = IIf(pBooShort, "oFlo", "Float odbc")
      Case 22: GetDataType = IIf(pBooShort, "oTime", "Time odbc")
      Case 23: GetDataType = IIf(pBooShort, "oDatT", "DateTime odbc")

      Case 101: GetDataType = IIf(pBooShort, "att", "Attachment")
      Case 102: GetDataType = IIf(pBooShort, "mvByt", "Byte MV")
      Case 103: GetDataType = IIf(pBooShort, "mvInt", "Integer MV")
      Case 104: GetDataType = IIf(pBooShort, "mvLng", "Long Integer MV")
      Case 105: GetDataType = IIf(pBooShort, "mvSgl", "Single MV")
      Case 106: GetDataType = IIf(pBooShort, "mvDbl", "Double MV")
      Case 107: GetDataType = IIf(pBooShort, "mvGuid", "Guid MV")
      Case 108: GetDataType = IIf(pBooShort, "mvDec", "Decimal MV")
      Case 109: GetDataType = IIf(pBooShort, "mvTxt", "Text MV")
  
      Case Else: GetDataType = Format(Nz(pDataTypN), "0")
   End Select
End Function

then
Debug, Compile from the menu in a module.

if you have no errors, great! If code you have written in other modules has errors, it will be highlighted so you can fix it. Keep compiling until nothing happens (good). then Save. Give the module a logical name like mod_Public or mod_ShowFields


click in the RunShowFieldsForAllTables sub
press the F5 key to run

OR, if it is just one table, change RunShowFieldsForTable and then run it


then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

thanks!
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:55
Joined
Feb 28, 2001
Messages
26,996
Which code should I provide? On open event?

What (physically) do you do to trigger a search? If it is to fill in the search box, then whatever code follows that search box, whatever event it might be. Sounds like it might be related to a Click event? But whatever that code does, there is where you need to concentrate.

COMPLETELY agree that if you have extraneous characters in names, you MUST clean them up to fix the problem. Otherwise, you will face massive issues in either missing some people you shouldn't have missed, or using something that KILLS the use of that index and eats your socks for you.
 

Users who are viewing this thread

Top Bottom