takes long time to pull in from table linked to Excel (1 Viewer)

gakiss2

Registered User.
Local time
Today, 04:43
Joined
Nov 21, 2018
Messages
168
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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:43
Joined
Feb 19, 2013
Messages
16,553
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
 

gakiss2

Registered User.
Local time
Today, 04:43
Joined
Nov 21, 2018
Messages
168
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

Registered User.
Local time
Today, 04:43
Joined
Nov 21, 2018
Messages
168
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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:43
Joined
Feb 19, 2013
Messages
16,553
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.
 

gakiss2

Registered User.
Local time
Today, 04:43
Joined
Nov 21, 2018
Messages
168
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?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:43
Joined
Feb 19, 2013
Messages
16,553
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
 

gakiss2

Registered User.
Local time
Today, 04:43
Joined
Nov 21, 2018
Messages
168
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.
 

Users who are viewing this thread

Top Bottom