Ms Access form to run query on SQL

Hi Expert's,

I need your help to create a Access Form to run below query into Microsoft SQL server, i am new for MS access, i build query on Excel but client wants that in Access form only,

Could you please help me to create a Access Form with below query , thanks!

And i set 30 seconds wait time to response back from the Server, if not connect then i would like to show some MSG box, is that possible in Access from



SELECT TOP 10000 [PriKey]

      ,[ShipBy]

      ,[Priority]

      ,[Tech]

      ,[ShipMethod]

      ,[InShopDate]

      ,[WONumber]

      ,[Jobs]

      ,[Patient]

      ,[AccountNumber]

      ,[Orthosis]

      ,[Casts]

      ,[Day]

      ,[Comments]

      ,[DesignDate]

      ,[Completed]

      ,[Stage]

      ,[SpecialNotes]

      ,[Bottom]

      ,[DateIn]

      ,[StandardsTime]

  FROM [FabDesign].[dbo].[Schedule]

Open in new window

LVL 3
LearnReporting Automation ExpertAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
I haven't dealt with Excel but in Access its quite simple.. just create a Passthrough query...paste the SQL and use this query as recordsource for your form.
LearnReporting Automation ExpertAuthor Commented:
Hi Jhon,

Thanks but my client want a button to be created on form, when they click but this need to run and get output either in Access or excel (preferred)
John TsioumprisSoftware & Systems EngineerCommented:
A button ...whatever whatever the client wants
Private Sub SQLRecordsource_Click()

me.recordsource = "TheName Of the Passthrough"

End Sub

Open in new window

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

LearnReporting Automation ExpertAuthor Commented:
Hi Jhon,

Sorry if you don't mine could you please advise with steps, if possible create file with coding and shared with me, i have tired but don't know where to select and create a forms,

In Excel VBA it will be in insert but in Access i don't know sorry
John TsioumprisSoftware & Systems EngineerCommented:
Well lets take it from the start
Create an Access database
Create a DSN pointing to your SQL (Administrative Tools -->Data Sources (Be carefull if you don't have the 32bit mark then you need to create a shortcut from SysWOW64 to it "C:\Windows\SysWOW64\odbcad32.exe")
Hit the Ribbon Create Tab -->Query Design...close the Table selection....hit Passtrhough...paste your SQL --> Propertiy Sheet (should be open to the right) -->select the DSN you have created...(ODBC Connection Str)
Run the passthrough.
So far so good
Ribbon -->Create -->Form Wizard --> Select the Passtrough --> select all the fields --> next -->Tabular ---Name it and finish
Put a button on the form somewhere on the header or footer
Go to form's properties -->Data--> delete  the Recordsoure...it should point to your query.
Paste the code i wrote
Save
Reopen the form...hit the button --> Presto...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LearnReporting Automation ExpertAuthor Commented:
Many thanks John, will try and keep you posted
LearnReporting Automation ExpertAuthor Commented:
Many thanks John
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.