'Make Table' Query to Linked BE

[yus786]

Hi

I have a make table query which creates the new table on the local front end.

I have now split the DB, so need to know how I can get this query to make the table on the BE i.e. how do i link it

Any ideas?

Thanks

 

[GinaWhipp]

Instead of a Make Table query why not create a Table and DELETE and APPEND accordingly? If that won't work please explain why? As depending on the amount of times this is done you could cause severe bloating of your database if not compacted regularly.

 

[yus786]

Instead of a Make Table query why not create a Table and DELETE and APPEND accordingly? If that won't work please explain why? As depending on the amount of times this is done you could cause severe bloating of your database if not compacted regularly.

Hi there, let me explain. This DB was given to me to maintain but written many moons ago

OK, so a temp table is created based on certain criteria's. These are timesheets. A form is then created based on this temp table

Admin 1 will populate the timesheets for that week. Then the temp table is appended to main table (linked) and payments/reports etc are run.

This is was OK when we had 1 user (with 1 FE).

Now we have 4 FE and 4 users. The above routine will work but ONLY on the FE that makes the table and hence only they can enter the timesheets.

I want all 4 users to enter data, hence need to link this temp table

What do you think?

 

[GinaWhipp]

Then you need to split the database and put the backend on the Server and the front ends on everyone's Local. Then they can all enter data. Not sure why it was created with a Temp table to append to another table but you can have one person do the appending. More about split databases here...

Does everyone have their own front end? Are their front ends installed on their drive and the back-end on a shared drive? Is the database opening in shared mode? Perhaps the below will help…

http://www.kallal.ca/Articles/split/index.htm

http://allenbrowne.com/ser-01.html

http://www.utteraccess.com/wiki/index.php/Sharing


You may also want to take a look at MVP Tom Wickerath's article…

http://www.accessmvp.com:80/Twickerath/articles/multiuser.htm


To handle the update of multiple front ends see…

http://www.autofeupdater.com/
http://regina-whipp.com/blog/?p=184
http://www.btabdevelopment.com/ts/freetools


If each user needs Access you can install the Runtime…

Access 2007
http://www.microsoft.com/downloads/...d9-9dc6-4b38-9fa6-2c745a175aed&displaylang=en

Access 2010
http://www.microsoft.com/downloads/...cd-5250-4df6-bfd1-6ced700a6715&displaylang=en

Access 2013
http://www.microsoft.com/en-us/download/details.aspx?id=39358

 

[yus786]

Sorry forgot to mention, they are already split

Old set-up was 1 X FE and 1 X BE. The TEMP table was not an issue as only 1 user

New set-up is 4 X FE and 1 X BE. The TEMP table is therefore only created on the one of users, which means the other 3 cannot see this temp form

Any suggestions how to get this temp table linked on the BE?

Thanks

 

[GinaWhipp]

You would need to link to it from the backend and then you would see 3 or 4 linked tables in the backend. I am still strying to understand why the Form isn't attached to the live table for everyone to enter into. Do you know why?

 

[yus786]

You would need to link to it from the backend and then you would see 3 or 4 linked tables in the backend. I am still strying to understand why the Form isn't attached to the live table for everyone to enter into. Do you know why?

Sorry don't know why. It was given to me like this. But has been working really well for years. Now due to the number of admin users, the requirement is to have 4 FE

I'm confused now

Just to highlight the challenge, all tables are linked apart from this temp one which is created from the FE that triggers the query

So how do i either link the temp table to the BE (no user intervention) or how do i make the table linked (ie make the table on the BE)

What about macros?

 

[GinaWhipp]

You don't link the tables to the Backend. You either put a link in the Backend to the Tables in the Individual Frontend or you run around gathering data from the individual Frontends. I know you think it should be easier but becuase this database was set up rather odd, IMHO, those are your only two choices.

 

[yus786]

You don't link the tables to the Backend. You either put a link in the Backend to the Tables in the Individual Frontend or you run around gathering data from the individual Frontends. I know you think it should be easier but becuase this database was set up rather odd, IMHO, those are your only two choices.

OK thanks bud

I have been looking at creating a macro (ImportExportData) that can link to a table in the backend

So temp table created in BE, and then i can create a macro to link it

Works good BUT the BE has a password and i cannot see anywhere in the macro fields where i can set the password

Would you know?

 

[GinaWhipp]

You can't... you need code.

 

[yus786]

You can't... you need code.

Great, so it can be done. Would you know how to code? If not, i'll open a thread in the VBA section.

Long winded i know but it's not my DB to strip apart, and this timesheet is just a small part.

So i can do this

1. FE 1 will run QRY to MAKETABLE
2. Macro will link to the BE
3. FE 1, 2 ,3 and 4 enter changes
4. FE 1 run reports
5. Macro to delete the table (link)

 

[GinaWhipp]

I think we are not understaning each other, so let me try this...

1. FE 1 will run QRY to MAKETABLE

1. Yes you can do that BUT unless you transfer it to the Backend there will be no way for everyone to see it.

2. Macro will link to the BE

2. You cannot link table made in (1) to Backend. You must create a link from the Front ends to the back end.

3. FE 1, 2 ,3 and 4 enter changes

3. Yep you can enter changes but they will in the Temp table in the inidivdiual Front ends only

4. FE 1 run reports

4. See (2) and (3)

5. Macro to delete the table (link)

5. See (2)

I do not have code to connect to a password protected backend. Google it, I'm sure someone has done this and it's out there somewhere.

 

[yus786]

Hi

Took your advise and made it into a linked table and using the APPEND qry instead

Working brilliant, thanks for your help

 

[GinaWhipp]

No problem... glad to help!

 

[gblack]

Sorry I didn't see this sooner, but you can push a "make table" to your back end database file, from the front end...and it's super easy to do.

When you choose to create the make table query, you will be prompted for a table name. Below the table name prompt, you have two (radio button) choices: Current Database, or Another Database.

Choose: 'Another Database:' and then browse to set the path and file name equal to your split back-end file.

Easy peasy...

 

[GinaWhipp]

@gblack

While this is true, and thank you for poiting that out, the *real* issue is with amount of times this table will be created there will be bloating thereby causing another problem. Then there's the, what if you add another Front end. Oh, and doing this 4 times you would still need to merge and append anyway. So, just doing the one link and the append is a far better option.

 

[gblack]

There are definitely pluses and minuses with everything.

One benefit of a make table is that it's a one object solution, that takes less VBA to code for and less work to implement.

As far as bloating goes... if you're copying and compacting your Db file on a regular basis, that shouldn't be an issue (never has been with me and I do, use some make table queries in my split DBs) unless you have a ton of users all hitting that make table query all the time... but if you do that's a whole other issue... and you probably shouldn't be doing either, cuz you'll be fighting people for the values in the table. In that case keeping it on the front end might be more advantageous.

To support your point, one added benefit of appending is: once you've made the table and use an delete/append, the Data Types in the table remain static. When you're remaking a table each time (via. a make table query) MS Access sometimes takes liberties with translating the datatypes and sometimes converts text datatype values into a "Memo" datatype field, causing all sorts of mismatch-in-data-type errors all over the place.

So yeah, it could be that appened is the better option... but not always imho.

One thing to note about this site is: There have been tons of times I have asked to do something and someone gets on and tells me a shuoldn't be doing that thing, without any real knowledge of the entire scope of the project, what it is that I am working on and what I have inherited. Often times the "best practices" way of doing business isn't really feasible. Sometimes you just need a band aid, so you can move along to more important issues. That said I like to give folks the answers they're asking for vs. why I feel they should do it another way.

I undertsand we have a whole slew of awesome programmers on this site who know the best way to do [insert any task here]... and I am not docking any of that. I have been helped countless times by the folks here and it is very much appreciated...

But sometimes I'd just like someone to say: Here's how you can do X... but here's an alterntive solution that is probably better for you in the long run... vs. telling me I shouldn't do X.