Making a new table from a SQL Server Query to MS Access.

[Rudurk]

Hi,

I am working on a table that has millions of records located on SQL Server unfortunately the table is dropped and remade everyday so they don't index this table.

It takes a while to query this table because it is not index. It contains appointment information from all departments of course we only care about our department.

So when we run a query from our Access DB (Using Passthrough) it still takes couple of minutes or so to pull our 23,000 odd Appointments.

Running a Make Table query on that Passthrough query is easy, but still takes time.

Is there anyway we can ask SQL Server to make a new table on our Backend Access DB??

I have a feeling it will be able to make that table almost instantly...

Something like

SELECT * 
INTO [Local Past Appointments] IN '\\New-Storage\NewDB\Backend\Data\Backend -DO NOT DELETE-.mdb' 
FROM dbo.OPA AS A
WHERE (([ApptDate] <= '2014-14-8') 
AND ([Appt_Res] Not Like '%AntiLiner%') AND (([Appt_Slot_Type] Like '%Liner%' OR ([Appt_Type] Like '%Liner%')))) 
ORDER BY A.ApptDate DESC;
 

[Rabbit]

Whether you make the table using SQL Server or Access, it will not be instant. It will need to transfer those millions of rows and that will take time.

[Rudurk]

@Rabbit

I realize that it won't be instant, but I think the server would create the table much faster than Access querying the server and doing it on my slow computer, and it won't be millions the server table has millions, but our department is probably must way less you gotta understand we are only one department out of 100s in our company.

So mm do you know how to do it? Sorry for the late reply I don't work on weekends.

[Rabbit]

It won't matter which one you use to create the table in Access, the speed will be similar. The bottleneck is not which program creates the table, it's the network speed.

Unless you are saying that the Access database is on the same file server as the SQL Server, then that will be faster. But if the Access database is on a different server than the SQL Server, which program you choose to create the tables won't make a big difference in how long it takes.

[Rudurk]

@Rabbit

Access Database is Not on the same file server.. but it is on another Server.. Basically we have two servers (Probably a lot more, but one that concern us)
Shared Information Server(SQL Server)
Our Server(SQL Server + File Server)

It would be really nice to know How to do it.. So I can really determine the speed for myself.. and not pursue doing something like this again.

I have a feeling the Network Speed is throttled for workstations..

[jforbes]

I think this is what you are asking for...If you have SQL Server Management Studio installed, you can us the SQL Server Import and Export Wizard by Right Clicking on your Database, Selecting Tasks, then Export Data. This will get you into the realm of
http://en.wikipedia.org/wiki/SQL_Ser...ation_Services
and
http://technet.microsoft.com/en-us/s.../cc511477.aspx


Another thing to consider is that a large portion of your bottleneck is by the liberal usage of Likes along with wildcards in your Where Statement:
…AND ([Appt_Res] Not Like '%AntiLiner%') AND (([Appt_Slot_Type] Like '%Liner%' OR ([Appt_Type] Like '%Liner%'))))

If you have any control of the Select statement that creates the table at night, you could create a bitwise field or a few that has this as its source like so:

SELECT 
  Appt_Res, Appt_Slot_Type, Appt_Type
, CASE WHEN CHARINDEX(Appt_Res, 'AntiLiner') = 0 THEN 1 ELSE 0 END AS Appt_Res_Not_Like_AntiLiner
, CASE WHEN CHARINDEX(Appt_Slot_Type, 'Liner') > 0 THEN 1 ELSE 0 END AS Appt_Slot_Type_Like_Liner
, CASE WHEN CHARINDEX(Appt_Type, 'Liner') > 0 THEN 1 ELSE 0 END AS Appt_Slot_Type_Like_Liner
FROM OPA
 

This should slow down your nightly process negligibly, but speed your selects up considerably.

[Rabbit]

First you need to install the Access drivers on the SQL Server. Then you can use SQL Server Integration Services to create a job to export the data. You can also schedule the job through the SQL Server agent.

[zmbd]

Actully, why not have the SQL-DBA create the VIEW in the server backend for you with the required parameters. Then you only need to link to that view, the SQL-Server should pull the information fairly quickly (compaired to the other methods (^_^) ) and then you can query the information to your heart's content on the ACC-Frontend

The nice thing here, is as the table is dropped, the view will toss an error; however, so long as the replaement table is in the same structure, the view becomes available again:

SQL Server - View
If a view depends on a table or view that was dropped, the Database Engine produces an error message when anyone tries to use the view. If a new table or view is created and the table structure does not change from the previous base table to replace the one dropped, the view again becomes usable. If the new table or view structure changes, the view must be dropped and re-created

This is what I asked my DBA to do, works very well.

[Rudurk]

Oh OK.. Thanks.. Well we have asked the DBA do it before, but he didn't seem to want to do it as if he makes one for us all the other departments are gonna want the same thing.. What he did say however was.. that they could push our appointments to our server every time they create their table again.. Unfortunately my department is to scared to allow something like that, which is why I am having to go through this kind of procedure.

I even thought of creating a linked server in our database and getting our server to query the shared server and make a local table, but again I don't have those rights YET!!.. I have just joined this department and my Senior Manager who has access to it.. is a bit too scared. As he isn't really a Database person. Before I came to this department everything was done using modules in access. I create stored procedures/functions to do those things, but every time I had to implement them I had to convince him that it doesn't do any harm and show him the code and show him nowhere in my code does it says delete/drop or alter LOL... then show him how much faster it'll do the job.. which is when he says OK and lets me execute the code on his pc as he watches me do it. The first time he saw Grant Execute on 'Procedure' to Public... he was like WHATS THAT???.

So exporting from SQL Server Management is kind of out of the questions as only my Senior manager has access to it.

Nevermind I'll have to make do with Passthrough query and make tbl in access on that query... and warn users that this is going to take a few minutes.. It's just our users are really not IT literate, and if it's gonna take 30 seconds to a minute to make a table they will probably click everywhere on the screen and end access thinking it's crashed.

Sorry for the late reply... I was not at work yesterday, was on a scheduled holiday.

[Rudurk]

@JForbes interesting... i'll give it go and see... what happens thanks... I have never though of doing it like that before.

[Rabbit]

JForbes suggestion is the same as mine, just be aware that modifications may need to be made to the server, i.e. it must have the Access drivers installed, otherwise it won't work. It is not installed by default and sometimes DBAs are wary about installing them.

[zmbd]

If you're going to run thru what jforbes mentions with SQL Server Management Studio, then you might be able to create the view on the SQL Server yourself... views are quite easy to setup, I didn't see which version your SQLServer is on; however, the method is still basically the same: Create Views (SQL Server 2014 )

Every once in awhile my DBA will grant me rights to do this for special projects; however, I've been with the company for a few decades so there's a trust factor that it sounds like isn't there for you; however, if you are comfortable doing this, ask for a one time elevation of privlage - worst that can happen is they tell you, "NO"... then you can ask them why they are not willing to be a team player and help streamline the access to the information stored... };-)

[Rudurk]

Hi Again,

Thanks for helping me... I am going to write the view and send the script to DBA and ask him to please Run it.. I am going to try to add what JForbes has mentioned... I hope it works coz I have no way of testing the code i'll literally just have to trust that I have written it write and send it off... if it FAILS.. I am gonna be so depressed... I'll let you know of the outcome soon. Thanks a lot.

[Rudurk]

I have just been in contact with the DBA asking him if I wrote the script would he run it... and looks like his in a good mood.. bcoz his just said Yeah!!!!!.. and that he would be happy to let me have the privilege of altering it... and he'd even try to fine tune it if he can.

:) Better result that I was expecting.

[Rudurk]

I opted to go for a Indexed View... And It's so good... almost instant results... Thanks a lot to everyone.. Unfortunately I can only select one answer, but I sincerely thank you all for your help.