Importing from excel (1 Viewer)

Danny

Registered User.
Local time
Today, 12:23
Joined
Jul 31, 2002
Messages
121
Thank you both.

Crystal, thank you for providing the code. I’m still in the process of cleaning up the data before proceeding with updating the table. The excel file provided to us contains both the existing providers along with the new providers. I’m trying to determine the best way for this proceed.

1. Filter out new providers and append only those providers

2. Try to bring everything hoping access will not import providers that already exist in the current table.

The Doc-Man,

Attached is a screenshot of the form users get when they create a new case. They will type in the first few letters to see if the provider already exists.

The other thing I noticed when creating a new record beside searching if provider already exist is to check if provider is in the excluded list. There is a exclusion table, but I’ve to find out how this table is updated. There is also a l link one can check if a given provider is in exclusion list “Application.FollowHyperlink "http://......" I wonder this is one of the reasons for slowness.

Following is the code on click event of the search button

Code:
Private Sub cmdProvSrch_Click()
'Open up the Provider Results form
On Error GoTo Err_cmdProvSrch_Click
    
    Me.Visible = False
    Forms!frmPICTSMain!txtProvNM2.Value = Me.cboProvider.Column(0)
    Forms!frmPICTSMain!txtProvNPI2.Value = Me.txtNPI
    Forms!frmPICTSMain!txtProvNO2.Value = Me.txtProvNO
    Forms!frmPICTSMain!txtLocNO2.Value = Me.txtLocNo
    Forms!frmPICTSMain!txtType2.Value = Me.txtType
    
    'Const strcQryNm As String = "qryCompareLEIE"
    Dim db As DAO.Database
    Dim con As Object
    Dim ProvFlg As String
    Dim rstProv As DAO.Recordset
    
    ProvFlg = Me.cboProvider.Column(0)
    
    If IsNull(Me.cboProvider.Column(0)) = False Then
    'If Me.cboProvider.Column(0) = [tblLEIE].[BUSNAME] Then
        Set db = OpenDatabase("Z:\Name of DB folder\Name of DB_be")
        Set rstProv = db.OpenRecordset("tblLEIE")
        With rstProv
            rstProv.Index = "BUSNM"
            rstProv.Seek "=", ProvFlg
            If rstProv.NoMatch = False Then
                MsgBox "Provider Is (LEIE)", vbCritical + vbOKOnly, "PROVIDER ON LEIE"
                Application.FollowHyperlink "http://......"
                Cancel = True
                Exit Sub
            Else
                Exit Sub
            End If
        End With
    End If
        
Exit_cmdProvSrch_Click:
    Exit Sub

Err_cmdProvSrch_Click:
    If Err.Number = 2105 And Err.Description = "You can't go to the specified record." Then
        Resume Next
    Else
        MsgBox Err.Description
        MsgBox Err.Number
        Resume Exit_cmdProvSrch_Click
    End If
      
End Sub
ProviderSearch.png

TIA

Regards,
 

arnelgp

error reading drive A:
Local time
Tomorrow, 00:23
Joined
May 7, 2009
Messages
9,336
You can significantly improve the performance of your Access database by maintaining an open connection to the back-end database throughout the time your front-end database is opened.

By forcing Access to keep the linked table's database open, Access avoids creating a new lock on the backend database every time one of its tables is used. This lets you open tables, forms, and reports much faster. Over a network, you'll usually see a substantial improvement with how quickly a form opens when it's based on a linked table.

https://www.access-programmers.co.uk/forums/threads/importing-from-excel.310607/#post-1683106
I just saw the code just now, I mean, why do you need to search?
unless the rowsource of your combo is from another planet.
also, you only do the searching if .ListIndex of the combo = -1 and the combo Is not Null.
for you know, the Value in the combo is Valid item of the combobox.
 
Last edited:

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 11:23
Joined
Feb 28, 2001
Messages
16,838
Well for one thing, I don't understand what index you are using because it wasn't in the display you showed us earlier.

Code:
rstProv.Index = "BUSNM"
BUSNM isn't in the list of indexes you showed us (four of them). I'm surprised that would even run... or does it give you an error 2105, which you skip?

Usually, if you are going to assert an index, you have to name the index. That name does not appear in any exhibit you have given us. Using the wrong index would probably cause a lot of confusion. I know it has confused me.
 

Danny

Registered User.
Local time
Today, 12:23
Joined
Jul 31, 2002
Messages
121
Thank you both.

Arnelgp:
Where do I check to see if the person who design the db maintained an open connection to the back-end db?

As for the reason behind for ‘searching’ my understanding is:
1. to see if the provider is in exclusion list (there is a table that stores the excluded providers)
2. to see if the provider already exist, if not to create a new provider.

The current provider table consists of 72K plus records and when you click on the dropdown you do not see the complete provider list (stops around the alphabet R). I’m not sure if this has something to do with access limit to 65K.

The_Doc_Man
I see your point, I think you are referring to screenshot #1. tblLEIE contains excluded providers (screenshot #2). Do I need to include the BUSNM as index to the tblproviders index?

TIA

Regards, BUSNM.png
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 11:23
Joined
Feb 28, 2001
Messages
16,838
Your recordset opens the right table for that field AND names a valid index. You just hadn't showed us, which is why the confusion. Now that it is where we can see the table and recognize what you are opening, I see nothing syntactically wrong. Is that the table that takes such a long time for your search?

I will make a suggestion that some people will probably object to. In your subroutine, if you open something, you should usually close it as a matter of a good habit. Access SHOULD clean that up for you when it hits the "Exit Sub" or "End Sub" so it SHOULD be transparently handled. However, we had a discussion with a Microsoft S/W engineer several months ago in which he suggested that at least CLOSING recordsets and databases (when you were done with them) was a good idea having to do with some recently discovered bug. It might have absolutely ZERO effect on your speed issue, but it is still a good habit to understand when cleanup should be considered.

You also commented on the number of records in a combo box. From personal experience, anything over a thousand selection options needs help because it becomes an overwhelming and totally unwieldy amount. If there is a way to "pre-qualify" the selections, you could use the concept of a "cascading combo box" (which you can look up in this forum) to pre-reduce the number of selections. That's just a thought, don't take it as a criticism.

If Arnel doesn't answer your question, I'll give a couple of likely places. If your DB has an opening form that stays open (behind the scenes OR out in the open) then it might have such a persistent connection set up in the Form_Open or Form_Load routine. Any form that actually closes would not be a likely candidate, but a form that minimizes itself or makes itself invisible (and then hangs around for the duration of the session) might be where such a thing is done.
 

Danny

Registered User.
Local time
Today, 12:23
Joined
Jul 31, 2002
Messages
121
Hi,

Sorry about the confusion.

Is that the table that takes such a long time for your search?
To answer your question, I’m not 100% sure. But, my understanding, or the intention is when creating a new provider case the system was to:
1. check if the provider is in the exclusion list. The exclusion table currently contains (about 5k records).

2. see if a record about the provider already exists in the DB. Currently the provider table contains 72k records. If none of the above, then a message will be displayed prompting …the provider doesn’t exist, would you like to create a new record…?



Users then answer yes, and the record will be created.

I’ll investigate cascading combo box.

TIA
 

arnelgp

error reading drive A:
Local time
Tomorrow, 00:23
Joined
May 7, 2009
Messages
9,336
limit the rowsource of the combo to just provider that are not Excluded.

select providerTable.* from providerTable Left Join ExclusionTable On providerTable.[Prov#] = ExclusionTable.[Prov#] Where ExclusionTable.[Prov#] Is Null;

then, no need to search on Exclusion table.
the only test you need to make if the Value in the combo is New provider.
this can be easily identify using

IsNull(combo) = False And combo.ListIndex = -1, then you add New Provider.

so you see, you can implement it Without searching, since All the
Items in the combos are Valid and not in Exclusion list.
 

Danny

Registered User.
Local time
Today, 12:23
Joined
Jul 31, 2002
Messages
121
Hi Arnel,

Thanks for your suggestion. I'll definitely try to implement your method. I just finished cleaning up the excel file and successfully appended new providers to the database. Now, the provider list grew to 105K. I'll post back if I still notice any performance issues.

I appreciate all your help!

Regards,
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:23
Joined
Feb 19, 2002
Messages
28,775
We are more than 20 posts into this question and so far you don't have a definitive answer. There is some worthwhile instructional advice. But, I'd like to make a couple of comments.
1. Using special characters such as the # in names is poor practice. You may be in a position to change them before you go further and should if you can. If you can't then put a post it on your monitor to remind yourself to not create names with special characters OR embedded spaces.
2. You need to have a unique index on the ProviderName. Unless your business crosses state lines, all the providers in your state will have unique names. If your data crosses state lines, then use a compound index of ProviderName and State OR ProviderName and address line 1. You absolutely do NOT want duplicate provider names.
3. If you are searching using Like, you may not be using an index to perform the search anyway. This can't be helped if you have to give the user this flexibility. The LIKE will almost always force a full table scan and so the number of rows in the table will impact the speed of the search.
4. Why is Excel the "master" for your provider list? The Access database should be the master. There is no good way to update the Access table with the data in the spreadsheet for several reasons.
a. You have to match on both name and street address and if one of those changed, you will add a duplicate record rather than updating an existing record.
b. Because the Excel spreadsheet is not updatable as a linked table, you will have to import the spreadsheet prior to doing the update.
5. Since you have to import the spreadsheet first, I suggest doing a careful search for duplicates in the spreadsheet and then create a unique index on provider name and address line 1 (if necessary). If you have to create the 2-field unique index, you will need to do it with the index dialog. You cannot do it in the table properties.
6. You have a generic Access setting that is creating indexes that are not necessary. Go to File/Options and remove the automatic index creation.
7. You should also spend some time examining the existing provider table and search for duplicates.
8. When you join the imported spreadsheet to the existing table and match on providername and addr1, keep in mind that minor differences in the strings will prevent a match so this is going to be harder than you think. For example a period after Inc in one table but not the other will prevent a match. If you need help on this match, I can make suggestions on how to do clean up first in order to get better matches.
 
Last edited:

Danny

Registered User.
Local time
Today, 12:23
Joined
Jul 31, 2002
Messages
121
Pat, thank you for your detailed explanation.

1. Special character: I understand your point, but as I stated in the past the database was created by someone else who no longer with the company. What makes things harder to troubleshoot is that there was no data dictionary and or documentation about the workflow etc.
2. Yes, there is a compound index providerName and Location
3. ok
4. The data is captured in mainframe environment and exported as csv
5. Yes, I created the 2-field unique index
6. Ok
7/8. I did spend some time examining the existing provider table to identify duplicates, data entry issues etc. even makes the search task very inefficient. e.g. provider name was entered as .arleen instead of Marleen, \illiam instead of William, ‘oseph instead of Joseph etc. I'd to validate and cleanup these before I completed the import process.

Finally, I appreciate all the feedback I received from this forum and I use every suggestion as a good resource and reference moving forward.

Regards,
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom