Using data from other data bases in a "main data base"..any problems

[Mike375]

If my make queries in the data base and the source data base is another .mdb and the table names in the other .mdb which would be used for the queries are the same as those in the data base where the queries would be made......does anyone see any problems with that in the area of corruption or similar.

The queries made would be indentical to their counterparts in the data base where they are made and would serve the same purpose.

It would be a toggle type of thing whereby the recordsources for the forms in question would be changed.

For what I want to do it works perfectly but I am not sure if there would be problems that would only surface with longer term use and varied conditions as opposed to some short term testing.

 

[namliam]

Why use make table queries at all? Why not simply use delete and append??? Less risk off problems as the tables dont get deleted and indexes and such things stay alive.

When you do this stuff take into account the "bloating" that access does. But if this is a single user environment, it should work without much if any problems

 

[Mike375]

They would not be MakeTable queries, all Select queries.

Part of this data base is for my telemarketing business and it is set to deal with 32 categories of prospect. One salesman might have 4 categories of prospect, another 3 etc. and in total the maximum is 32. I currently have 3 data bases (copies of each other but with different data) and each data base has 3 computers/network hanging of it. Thus 3 telemarketers per data base and we never get to the 32 categories.

I have an opportunity to do some other telemarketing work but it would be mixed with what is currently done. Thus one telemarketer would be doing both and that would result in too many categories. I could easily make a data base copy and put the other peoples name lists in there but the problem with that is the telemarketer could not do both groups of calling together....calls coming back to him etc., call backs he has to make with "pop up call back system" etc.

So I thought I would make a copy of the data base and put in the other people's name lists and with duplicate queries made in the data base the telemarketer is using and with those queries sourcing the data base copy hold the other peoples name lists.

I could of course link to the tables in the copy but that would mean different table names for the linked tables and thus more fiddling to make the dual set of queries.

Increasing the number of prospect categories beyond 32 is a lot of work becasue the displays/forms would need to change and the last thing I want to do is change displays as the telemarketers get very use to them.

 

[namliam]

In my experience the average telemarketeer is not the brightest person in the world... so I agree not changing to much.

For me it is very hard to get a mental picture of what you are trying to do... but creating copies of databases to have different categories sounds like cumbersome and unnecesary... More likely it is a problem with its current design.

I am pretty sure the problem goes back to the original design beeing limited to a set number of categories... well... yeah.. sounds like...

I am a bit worried tho about the "3 computers per database" how many sales people do you have? Will access be able to handle that many concurrent users if you made it all one DB?

Now going back to your question...
I think what you are trying to do is....
Your current situation you have 1 backend and different front ends for different agents correct??

Now you want to have these same agents connect to a different backend thus retrieving simular, but different data...

Is that more or less correct? If it is... It sure is possible.

 

[Mike375]

In my experience the average telemarketeer is not the brightest person in the world... so I agree not changing to much.

They vary greatly and according to the type of prospect and calling.

I am pretty sure the problem goes back to the original design beeing limited to a set number of categories... well... yeah.. sounds like...

I run into two problems when I go over 32 categories. One relates to tabular displays and what fits on the screen without scrolling. The second I run into is speed. One part of the thing has the goal settings for each category...hours of calling, number of calls etc. The other part records the outcome of each call. The telemarket has the option of seeing his results Vs goals on either the category he is calling or all categories he has and it updates after each and apears on the next record. Even wants it to update against all categories then the computer starts to big down after about 90 minutes. That is not a problem because no one goes more than 90 minutes non stop. He just shuts down Access while having a break and restarts it and all is well. The networks being on wireless does not exactly speed things up.

I am a bit worried tho about the "3 computers per database" how many sales people do you have? Will access be able to handle that many concurrent users if you made it all one DB?

There are 3 telemarkets each with a computer/db on network. Another 3 telemarkets each with a computer/db on a network and a third set. Each of the 3 telemarkets are like a self contained untit. So like 1 computer + 3 and 1 computer + 3 and 1 computer + 3

Now going back to your question...
I think what you are trying to do is....
Your current situation you have 1 backend and different front ends for different agents correct??

Now you want to have these same agents connect to a different backend thus retrieving simular, but different data...

Is that more or less correct? If it is... It sure is possible.

Sort of.

I want one of the telemarketers to be able to run two sets of data on the one data base. So there would be two sets of 32 categories but one is on another data base. In other words at any given time only 32 categorie is used.

I would make a set of duplicate queries but sourcing the tables on the other data base. A duplicate set of macros which are mainly about changing record source. I could make a macro that would run the others and change all the form record sources in one go. I can do that with the "pop up call back" as it will know which of the 32 categories the person is from then if required change the record sources so the record can be found.

By sourcing the tables in the duplicate data base the table names are the same so making the duplicate queries is easy. I would keep the queries names the same except for having a 1 or a letter in front of them and ditton for the macro names.

However I have never used queries based on soucing another data base and where the table names are the same as for the original set of queries. I have tried it on a small scale and it works OK but I don't know if this sort of things will cause any problems. As you know if I do it by table linking then I will have things like maintable1, calltable1 etc.

My feeling is that it would be the same as normal table linking but it just seems strange making queries on what amounts to two tables of the same name but at different tables

 

[namliam]

In my experience the average telemarketeer is not the brightest person in the world... so I agree not changing to much.

They vary greatly and according to the type of prospect and calling.

It is a gross generalisation offcourse... and will not be true... just my general observation... Dont put any more attention to this...

I am pretty sure the problem goes back to the original design beeing limited to a set number of categories... well... yeah.. sounds like...

I run into two problems when I go over 32 categories. One relates to tabular displays and what fits on the screen without scrolling. The second I run into is speed. One part of the thing has the goal settings for each category...hours of calling, number of calls etc. The other part records the outcome of each call. The telemarket has the option of seeing his results Vs goals on either the category he is calling or all categories he has and it updates after each and apears on the next record. Even wants it to update against all categories then the computer starts to big down after about 90 minutes. That is not a problem because no one goes more than 90 minutes non stop. He just shuts down Access while having a break and restarts it and all is well. The networks being on wireless does not exactly speed things up.

Access and networks... not the best of combinations unfortunatly...
Have you considered upscaling to a bigger database like MySQL or something?

I am a bit worried tho about the "3 computers per database" how many sales people do you have? Will access be able to handle that many concurrent users if you made it all one DB?

There are 3 telemarkets each with a computer/db on network. Another 3 telemarkets each with a computer/db on a network and a third set. Each of the 3 telemarkets are like a self contained untit. So like 1 computer + 3 and 1 computer + 3 and 1 computer + 3

3 users should be no problem for access ...

I would make a set of duplicate queries but sourcing the tables on the other data base. A duplicate set of macros which are mainly about changing record source. I could make a macro that would run the others and change all the form record sources in one go. I can do that with the "pop up call back" as it will know which of the 32 categories the person is from then if required change the record sources so the record can be found.

Duplicating everything? Surely not?? I hope not! LOL

You can go into the linked tables and relink them to another backend source without much problems. There is even a sample (I think) in the Access help.
Lookup the tabledefs object...
This way no different queries or such are needed. Except relinking the tables can take a few seconds...

However I have never used queries based on soucing another data base and where the table names are the same as for the original set of queries. I have tried it on a small scale and it works OK but I don't know if this sort of things will cause any problems. As you know if I do it by table linking then I will have things like maintable1, calltable1 etc.

My feeling is that it would be the same as normal table linking but it just seems strange making queries on what amounts to two tables of the same name but at different tables

It definatly works, I did this exact thing having 2 linked tables for 2 different sources but with the same (type of) data. It works like a charm... if a bit combursome...

Relinking the tables to another source does basicaly the same, but with (much less) headache I think.

 

[Mike375]

Access and networks... not the best of combinations unfortunatly...
Have you considered upscaling to a bigger database like MySQL or something?

No and for a couple of reasons. Even if I had something big I would still have the telemarketers split into independent groups. I have a basic mistrust of computers. The other disadvantage for me is that I can do all I need on Access. Sometimes I need the odd tip like that one you gave me today on find most common field values. Actually I wanted that to check these name lists from the other people. By being able to do it myself I can make instant changes.

3 users should be no problem for access ...

It is fine for what is needed. And as I said I prefer the idea of 3 sets of 3 on Access as opposed to 1 set of 9 on something bigger.

Duplicating everything? Surely not?? I hope not! LOL

Well so far that is the plan Fortunately all the queries and macros concerned are grouped together because of how I name them.

You can go into the linked tables and relink them to another backend source without much problems. There is even a sample (I think) in the Access help.
Lookup the tabledefs object...
This way no different queries or such are needed. Except relinking the tables can take a few seconds...

I don't know what "defs" mean after queries and tables. See mentioned on the forum from time to time.

Are you saying there is a way where I can just change which which data base is linked to by the one on the network. In other words .mdb A has all the details for "name list A" and .mdb B has all the details for "name list B" and I can quickly swap the linking to tables from .mdb A to.mdb B and back again. Would that be something that is simple for someone to do. I one alternative I was think of was to place a second set of tables containing the stuff pertaining to the other list and instead of having a second set of macro and queries have a macro that runs a few Append/Delete to shift the records in and out of the table. The only problem with that is it is a little slow and especially across the wireless. Changing which data base the table linking was done to is similar in principle but sounds much better.

I could both sets of names in the same tables for the two lots of 1-32 and put a marker in a field so a query could pick which lot of 1-32 a name is but that would be a fair bit of mucking about.

It definatly works, I did this exact thing having 2 linked tables for 2 different sources but with the same (type of) data. It works like a charm... if a bit combursome...

Relinking the tables to another source does basicaly the same, but with (much less) headache I think.

Yes, if I can change which data base to link to and quickly that would be easily the best way to go. However, the change would have to be able to be done by the telemarketer. Unfortunately (always the way) this new telemarketing we would take on is low level stuff and so the telemarketer is the same.

The problem with this sort of thing is if you are not careful you can spend way too much time looking for the easy way In fact to be honest..it is Friday night here...if I had just gone ahead this morning with the worst way it would now be done

 

[Mike375]

I just searched and Google brougt up plenty

tabledef access linking

A lot of it is different forums including this site

 

[namliam]

The tabledefs collection contains all the tables that are in your database, querydefs, Froms (why not formdefs??) are simular collections/containers.

Try opening the Access help and typing "Tabledefs" You should find plenty information there to (atleast) get started a good way...

In particular you would be intrested in the "Refresh link method" which you should find and there is a semi good example in there... (the RefreshLink Method Example, not the other one)

Are you saying there is a way where I can just change which which data base is linked to by the one on the network. In other words .mdb A has all the details for "name list A" and .mdb B has all the details for "name list B" and I can quickly swap the linking to tables from .mdb A to.mdb B and back again. Would that be something that is simple for someone to do. I one alternative I was think of was to place a second set of tables containing the stuff pertaining to the other list and instead of having a second set of macro and queries have a macro that runs a few Append/Delete to shift the records in and out of the table. The only problem with that is it is a little slow and especially across the wireless. Changing which data base the table linking was done to is similar in principle but sounds much better.

Yes that is what I am saying... Using queries to fetch data and stuff *ugh* talk about your basic *not such a good idea* (understatement LOL) Slow, bloating, Bah!
Dont like it at all...

It is a relatively easy process to do either manually or programmed in code. Code offcourse is faster and less prown to manual error. Also pre-programmed code can be as easy as a click of a button and wait some seconds for the process to finish....
You can even make a slider bar on a form to show the progress but that is nice to have I think Some eye candy...

I could both sets of names in the same tables for the two lots of 1-32 and put a marker in a field so a query could pick which lot of 1-32 a name is but that would be a fair bit of mucking about.

Not all of your data is in the backend?? Most stuff should be in the backend, except for some very small exceptions. If you make sure this data too is in the backend, then changing the backend would also change the categories....

if I had just gone ahead this morning with the worst way it would now be done

Then what are you on the forum for? Go ahead and do it allready

You are here offcourse because you want to not just do it, but learn a little while doing it
I might have a sample for you including a slider bar and stuff, but I wouldnt count on it.... Go read the help and see how far you get in probably less time than you would have done making your own thing

 

[Mike375]

Not all of your data is in the backend?? Most stuff should be in the backend, except for some very small exceptions. If you make sure this data too is in the backend, then changing the backend would also change the categories....

The table data is all backend and I considered dumping these new prospects and other associated stuff/tables and have two lots of 1-32 but with a marker field so queries could pick which 1-32 the person was. But is messy.

You are here offcourse because you want to not just do it, but learn a little while doing it

That is correct. I usually find as well that I pick up other stuff on the way through that might not even relate to the problem.

I might have a sample for you including a slider bar and stuff, but I wouldnt count on it.... Go read the help and see how far you get in probably less time than you would have done making your own thing

Well have some time now to try other things as I just made a quick fix, which I had not thought of, it is rough, very rough but functional. The dbs have a start up macro which opens a few forms and runs the pop up call system at start time. I put those in an autoexec. Made a little macro (stuck on each of the forms as a label) which runs a .bat file to open the other data base and then Quit. I made up a duplicate of the call back pop up system, a table, couple of queries, form, a couple of macros and did the couple of queries through the source other data base. I just have to work out a way so that while in db1 there is a connect that works the pop up call back because of the "time now" and what is in db2. The .bat file fully opens the other data base and closes current in about 7 seconds and the opening of the other data base triggers its pop call back so the appropriate record can be found.

As a side note searching on Tabledefs in Access Help only brings up Troubleshoot Visual Basic code and that did not seem to go anywhere. Google brings up a lot.

Thanks for help and ideas. I might call it quits for a while as just after midnight down here.

 

[Mike375]

PS,

If you are wondering how the telemarketer will know which data base (or table links as the case might be) to find a person who has called him back (averages about 1 in hour to hour and half from his messages) he can tell by which phone rings The phone number he gives out is displayed on each record

 

[namliam]

Refresh link help:

Updates the connection information for a linked table (Microsoft Jet workspaces only).

Syntax

tabledef.RefreshLink

The tabledef placeholder specifies the TableDef object representing the linked table whose connection information you want to update.

Remarks

To change the connection information for a linked table, reset the Connect property of the corresponding TableDef object and then use the RefreshLink method to update the information. Using RefreshLink method doesn't change the linked table's properties and Relation objects.

For this connection information to exist in all collections associated with the TableDef object that represents the linked table, you must use the Refresh method on each collection.

The example:

Sub RefreshLinkX()

    Dim dbsCurrent As Database
    Dim tdfLinked As TableDef

    ' Open a database to which a linked table can be
    ' appended.
    Set dbsCurrent = OpenDatabase("DB1.mdb")

    ' Create a linked table that points to a Microsoft 
    ' SQL Server database.
    Set tdfLinked = _
        dbsCurrent.CreateTableDef("AuthorsTable")
    tdfLinked.Connect = _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
    tdfLinked.SourceTableName = "authors"
    dbsCurrent.TableDefs.Append tdfLinked

    ' Display contents of linked table.
    Debug.Print _
        "Data from linked table connected to first source:"
    RefreshLinkOutput dbsCurrent

    ' Change connection information for linked table and 
    ' refresh the connection in order to make the new data 
    ' available.
    tdfLinked.Connect = _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=NewPublishers"
    tdfLinked.RefreshLink

    ' Display contents of linked table.
    Debug.Print _
        "Data from linked table connected to second source:"
    RefreshLinkOutput dbsCurrent

    ' Delete linked table because this is a demonstration.
    dbsCurrent.TableDefs.Delete tdfLinked.Name

    dbsCurrent.Close

End Sub

Sub RefreshLinkOutput(dbsTemp As Database)

    Dim rstRemote As Recordset
    Dim intCount As Integer

    ' Open linked table.
    Set rstRemote = _
        dbsTemp.OpenRecordset("AuthorsTable")

    intCount = 0

    ' Enumerate Recordset object, but stop at 50 records.
    With rstRemote
        Do While Not .EOF And intCount < 50
            Debug.Print , .Fields(0), .Fields(1)
            intCount = intCount + 1
            .MoveNext
        Loop
        If Not .EOF Then Debug.Print , "[more records]"
        .Close
    End With

End Sub

 

[Mike375]

Well I can now change table linlks via Data Base Utilities Link Table manager.

That alone is a gain as I never knew how do that. In the past I have deleted all the linked tables and gone back to Get External Data etc.

 

[Mike375]

I just tried that Link table Manager again but when I click Prompt for location and Select All it then wants to go one table at a time. But if I click on the box next to a few tables then it does those in on go.

 

[Mike375]

That's off the Microsoft Help?

I had only got as far as the Tools/Link Table Manger deal off Help

 

[namliam]

That's off the Microsoft Help?

I had only got as far as the Tools/Link Table Manger deal off Help

The linked table manager is a PAIN, which is why you need the code...

No the previous post is something I typed up out of the top of my head...
YES offcourse it is the microsoft help.... and it should really help you!

 

[namliam]

Well I can now change table linlks via Data Base Utilities Link Table manager.

That alone is a gain as I never knew how do that. In the past I have deleted all the linked tables and gone back to Get External Data etc.

This is actually (short of the coding thing) the easiest and most sure way of doing it right...

 

[Mike375]

It won't work with Select All. Perhaps too many tables.

If I check about 25 manually it then wants to go through one by one.

No the previous post is something I typed up out of the top of my head...
YES offcourse it is the microsoft help.... and it should really help you!

I knew you did not just type out but just wondered if was something you had stored.

 

[namliam]

Just forget the linked table manager excists, and go for the code

Linked table manager you cannot use for your Agents I dont think... The code you can

 

[Mike375]

I just had a small, very small brainstorm

Lets say there are 15 tables involved (all linked)

Table1
Table2 etc

I link to the appropriate tables in the other data base so they come is as

Table11
Table 21 etc

A couple of macros with Rename action and Presto. Just swap their names.

I just did a couple and it is instant. Also worked OK on the table with 32 records that has referential integrity etc.