Pulling data from SQL Server using MS Access table 1

[MwTV]

Just to ensure that I am following the appropriate steps to query related information from a SQL Server database using data within a MS Access table.

I performed the following steps;

• Imported 4000+ records from MS Excel into MS Access table named "ContractNumber"
• Within MS Access, I linked to appropriate tables within the SQL Server database
• Create passthrough query to extract data from the SQL Server database for the 4000+ records in my Access table


So far, the query has been running for about an hour!

Initially, I had thought that I needed to create a temporary table in the sql server database using
the following syntax;

CREATE TABLE ##tmpInvNbr
InvNbr varchar(15) COLLATE SQL_Latin1_General_CP437_BIN)

INSERT INTO ##tmpInvNbr SELECT * FROM ContractNumber


Maybe, I am trying to use a passthrough query to extract data from a local table and also from
the tables within the SQL Server database is not the best way to go.

What am I missing here?

Am I following the appropriate steps to query the SQL Server database using the data from Microsoft Access?

If not, please provide some insight.

Thanks in advance.

 

[AlexCuse]

If you want to query your local access tables along with your SQL Server tables, I think that you will want to set them up as linked tables. I would put all your tables in SQL though, and continue trying to use the pass-through query.

You shouldn't need to mess around with temp tables, but if you do, please write this as a stored procedure and call it from access that way.

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[MwTV]

Querying using linked tables is extremely slow. For example, the query runs over an hour using linked tables.

Wouldn't the query perform faster if the data within the local MS Access table is imported into a SQL Server temporary table?

If so, how would I create the temporary table in SQL Server using the data that is contained within the MS Access table?

Any disadvantages to performing this?

 

[AlexCuse]

The only way you are going to be able to create and fill the temporary table in access is going to be to either use a linked server to query your access db from SQL (and reference this linked server in your pass-through query), or a DTS/SSIS package to refresh the tables on the SQL side.

Another option would be to have a permanent table that you can link to then run deletes and append to refresh.

But, why can't you simply put your whole database in one place (SQL Server)? Any option that you have is going to be a lot more work than simply moving your tables out of access and into SQL. Is there a particular reason you can't do this? If you set up the SQL tables as linked tables in access you can make this change transparent to the user.



[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[MwTV]

Just trying to maximize the processing that is performed at the server level instead of locally.

Initially, the 4000+ records were within an Excel spreadsheet. While the data was in the Excel Spreadsheet, I created a column next to it and specified something along the lines of - INSERT INTO ##tmpInvNbr SELECT '45785'

Then, I copied this down to the last row. Moved to SQL Query Analyzer and pasted into the query pane.

Long story short, I was not able to copy/paste all of the 4000+ records into the SQL Query Analyzer query pane without receiving an error that the text was too long, etc.

Therefore, I imported the 4000+ records into MS Excel.

It appears that the following two options are the most feasible;

Query using linked tables using the present setup
or Use Excel and just insert the data into the temp table incrementally - maybe, 800 records at a time.

 

[AlexCuse]

>>>Just trying to maximize the processing that is performed at the server level instead of locally.

As I said, I think you are best to have your database live in one place. You should not have half of it in access, and half of it in SQL Server.

If replacing the access tables with linked SQL Server tables (which, because they exist on SQL will be available t your pass-through query) is not an option, then maybe you should just do everything in access? 4000+ records is not so many that it's going to make the jet engine unbearable slow, and it might outpeform linked tables.

If you really want to get data into a temp table on SQL from excel/access, I suggest that you look into linked servers (for access) or openquery (for excel), but I think that DTS (for 7/2000) or SSIS (2005) will give you more stability in importing this data, doing any necessary cleansing, and refreshing your table.

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[MwTV]

Thanks for the insight.

Linking the tables was the preference.

Will look into linked servers and openquery.