VBA to load all the tables with sql server data (1 Viewer)

penchalas

Registered User.
Local time
Today, 20:31
Joined
May 14, 2019
Messages
26
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
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:01
Joined
Jul 9, 2003
Messages
16,244
I need more info, a deeper explanation.

Sent from my Pixel 3a using Tapatalk
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:01
Joined
Jan 23, 2006
Messages
15,364
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:01
Joined
Feb 28, 2001
Messages
26,999
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:01
Joined
Oct 29, 2018
Messages
21,358
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.
 

penchalas

Registered User.
Local time
Today, 20:31
Joined
May 14, 2019
Messages
26
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?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:01
Joined
Feb 28, 2001
Messages
26,999
Definitely possible. You want to make the old button transparent and disable it.

https://docs.microsoft.com/en-us/office/vba/api/access.commandbutton.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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:01
Joined
Oct 29, 2018
Messages
21,358
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:01
Joined
Feb 28, 2001
Messages
26,999
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.
 

Users who are viewing this thread

Top Bottom