Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-07-2018, 09:55 AM   #1
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 28
Thanks: 13
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
takes long time to pull in from table linked to Excel

I am working on a database that can pull data from a SQL server. The avenue that I have available is to use Excel's data connection function. I then create a linked table in Access which is linked to the aforementioned Excel with the data connection. I made a combo on a form that pulls from that linked table. It works but it takes incredibly long to pull in the data. This is replacing a static table of essentially the same size with practically identical data and the combo attached to that static table (generic access table) has practically zero lag time. I can start typing in a partnumber and it completes it faster than I could type it in.

It seems obvious enough that the linked table is making the difference. I would like to understand why and is there anything that I can do to make it as fast or at least much faster than it is now. The point was to be able to connect to the data in the background and not have to frequently update the static table. Background is that this is manufacturing and the table represents part numbers and suppliers. They change often enough you don't want to go many days without an update.

Assuming there is no quick fix, the next step would be a way to automate importing a table from the same source.

Thanks in advance for the help.

gakiss2 is offline   Reply With Quote
Old 12-07-2018, 10:16 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,472
Thanks: 40
Thanked 3,384 Times in 3,279 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: takes long time to pull in from table linked to Excel

the problem with linking to excel is that the connection needs to be made which is almost certainly what takes the time. But there are other considerations such as where the file is (on a network? what is network performance like?). How many rows? - excel doesn't have indexes, so sorting finding etc will take longer.

For porting in data, excel is fine (tho' better to use .csv) but as a record/rowsource for general forms, not so good.

I would have a routine to refresh your access table. This can be done with a macro in excel if you wanted to manage the whole thing in one place.

Alternatively, why not use the data connection function to have a linked table to SQL Server instead - although whether that is worthwhile will depend on sql server/network performance
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
gakiss2 (12-07-2018)
Old 12-07-2018, 10:22 AM   #3
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 28
Thanks: 13
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: takes long time to pull in from table linked to Excel

Thank You

The instructions to use the SQL connection were to pipe it to Excel. Our IT knits their brow quite furiously if they think one of us (non IT) are using Access in a way that threatens their purvey over everything informational. There is literally a policy which says I can't write an Access db that more than one person uses. Of course I violate that one daily. Sorry to ramble on about our IT. I'll check back in after try piping straight into Access.

gakiss2 is offline   Reply With Quote
Old 12-07-2018, 10:30 AM   #4
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 28
Thanks: 13
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: takes long time to pull in from table linked to Excel

OK so after just a quick look I may need some help to do that. In excel I can choose 'From other sources' then 'from sequel server'. When I go to Access external data there are several choices but I can't tell which would work. I see ODBC, data services and dBase files. there are others such as outlook etc. they seem, at least by their title to be not what I am looking for. For the Excel connection I have a server name, a database name then a username and a password. Any clues are appreciated.
gakiss2 is offline   Reply With Quote
Old 12-07-2018, 10:41 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,472
Thanks: 40
Thanked 3,384 Times in 3,279 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: takes long time to pull in from table linked to Excel

you would need the ODBC route. You may need to click on 'new to create it. If you can't your IT will probably need to create it, which given your comments is unlikely.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 12-07-2018, 11:56 AM   #6
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 28
Thanks: 13
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: takes long time to pull in from table linked to Excel

making some progress. I make new connection. I get "you are logged on with non-Admin privleges. System DSNs could not be created or modified.

Then click OK and new window. Select type. Only avail is User Data Source, System Data source is greyed out. Click Next.

Choose driver. I choose SQL Server

Next I name the datasource then describe it then choos server. Here I find the server that I put into the Excel wizard. Exact same name.

then I found the database name which is the same and make a connection.

I tried before and it only pulled in a single field when it asked which field to use use as a unique identifier. it was a field totally useless for that. Then I tried again and notices that there is the work 'Linking...' in the Link Tables dialog box. Its been there for several minutes now. Thought I would wait it out. In the mean time any thoughts, does it seem like I might be on the right path?
gakiss2 is offline   Reply With Quote
Old 12-07-2018, 12:11 PM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,472
Thanks: 40
Thanked 3,384 Times in 3,279 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: takes long time to pull in from table linked to Excel

could be OK - but should not take long to connect - may be caught is some sort of security loop.

not sure why you are only seeing a single field. Ideally you should have a field as a unique identifier

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
gakiss2 (12-07-2018)
Old 12-07-2018, 12:17 PM   #8
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 28
Thanks: 13
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: takes long time to pull in from table linked to Excel

yes some sort of security loop. or maybe something else but 'Linking...' stays until I choose 'NewRank as Unique Identiier . As soon as I do that I get the table with the single field consisting of NewRank which is not a useful field and is NOT unique to each record. About 98% of records have a value of 1. I had tried the same to import a table to access but had the same result (except of course I had an imported table rather than a linked one.). Maybe need to lower expectations and work on a daily Table Import from the Excel. and its Friday and even I am getting a bit tired of Access this afternoon.

gakiss2 is offline   Reply With Quote
Reply

Tags
automate , excel , import , linked table

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
An update query takes very long time gsrai31 Modules & VBA 24 05-22-2018 08:32 PM
.EOF takes a really long time! April15Hater Other Software 16 08-13-2009 06:27 AM
Form takes a long time to open Jean-Guy Forms 9 09-07-2006 07:40 AM
SP takes long time but reboot fixes it mmitchell SQL Server 13 01-05-2006 11:50 AM
FileSearch takes a long time hqengint Modules & VBA 1 07-26-2002 12:47 PM




All times are GMT -8. The time now is 06:38 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World