Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-13-2019, 02:28 AM   #1
penchalas
Newly Registered User
 
Join Date: May 2019
Location: india
Posts: 26
Thanks: 1
Thanked 0 Times in 0 Posts
penchalas is on a distinguished road
VBA to load all the tables with sql server data

Hi,


Below is my requirement


1. I have 5 tables created in Access (T1,T2,T3,T4,T5)
2. I have a button click event for each table which loads the data from SQL server

3. Now i need a single button which does this, instead of having multiple buttons


A single button which loads the data from SQL server DB to access tables. This should be handled in the click event only



How to achieve this

penchalas is offline   Reply With Quote
Old 06-13-2019, 04:02 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,271
Thanks: 527
Thanked 927 Times in 879 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: VBA to load all the tables with sql server data

I need more info, a deeper explanation.

Sent from my Pixel 3a using Tapatalk
__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 06-13-2019, 04:43 AM   #3
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,127
Thanks: 81
Thanked 2,009 Times in 1,957 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: VBA to load all the tables with sql server data

More info required.
Can you link to the SQL server tables directly in Access?
Perhaps you can tell us more of your environment and requirement in non-database terms, so we get the "bigger picture".
Good luck.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 06-13-2019, 04:53 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,346
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: VBA to load all the tables with sql server data

If you can view the code behind each button click event that individually loads the tables, you should be able to copy the "meat" of the code for each one. Then build another button and copy the transfer code. There will probably be some error handling code which you won't need to duplicate.

IF (and this is a very big IF) all of the code is on the same form AND none of the click routines do anything except transfer data, you COULD write the new button's routine to just call the five click routines by name as though they were subroutines. (They are.) This is generally not advised but if the five click routines are extremely simple-minded, it might work.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
Old 06-13-2019, 07:14 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,566
Thanks: 50
Thanked 1,046 Times in 1,027 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: VBA to load all the tables with sql server data

Or, you could even try it this way... For example, let's say you have five buttons like so:

Button1: Code to Import/Transfer to/from Table1
Button2: Code to Import/Transfer to/from Table2
Button3: Code to Import/Transfer to/from Table3
etc.

Then, you can create a new button does simply does the following:

NewButton: Execute Button1, then execute Button2, then execute Button3, etc.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-14-2019, 03:15 AM   #6
penchalas
Newly Registered User
 
Join Date: May 2019
Location: india
Posts: 26
Thanks: 1
Thanked 0 Times in 0 Posts
penchalas is on a distinguished road
Re: VBA to load all the tables with sql server data

Quote:
Originally Posted by theDBguy View Post
Or, you could even try it this way... For example, let's say you have five buttons like so:

Button1: Code to Import/Transfer to/from Table1
Button2: Code to Import/Transfer to/from Table2
Button3: Code to Import/Transfer to/from Table3
etc.

Then, you can create a new button does simply does the following:

NewButton: Execute Button1, then execute Button2, then execute Button3, etc.
Can we hide the old button and user sees only the new button?
penchalas is offline   Reply With Quote
Old 06-14-2019, 04:47 AM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,346
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: VBA to load all the tables with sql server data

Definitely possible. You want to make the old button transparent and disable it.

https://docs.microsoft.com/en-us/off...on.transparent

That link points you to a page for the .Transparent property, which you can set TRUE and make the button not visible any more. On the left of that page are some other button properties you might wish to examine to see what else you can do to buttons. You could also use the .Visible property, which is in that list. And to be sure that you don't accidentally click the old button, look at the .Enabled property.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
Old 06-14-2019, 05:27 AM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,566
Thanks: 50
Thanked 1,046 Times in 1,027 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: VBA to load all the tables with sql server data

Quote:
Originally Posted by penchalas View Post
Can we hide the old button and user sees only the new button?
You can also go to design view and set Visible property for the buttons to No. if youíre out of space, you can move all the invisible buttons to one spot and make them small.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-14-2019, 08:52 AM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,346
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: VBA to load all the tables with sql server data

Just remember if you do what theDBguy suggests, disable the buttons. Turns out there is a trick that you can put an enabled button on top of something even though it is invisible. I used to do that as a way to have a hidden action in a place that wasn't obviously an active control.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
Reply

Tags
multiple buttons , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Code Error when load data from server n3wguys General 2 06-06-2014 01:44 AM
Question Load data from two tables babaroga General 3 12-30-2008 02:38 PM
Upsize = good on server, but client won't load tables gomikem SQL Server 1 06-27-2008 02:56 PM
Load data from SQL Server into combobox (too slow) dealwi8me Forms 7 06-24-2008 04:36 AM
Getting data from SQL Server Tables from Access BigMikey Modules & VBA 1 07-29-2002 05:35 AM




All times are GMT -8. The time now is 07:07 PM.


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 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World