tranferring a database 1

[gwog]

Using Access 97.

I am trying to find an easy way to build a temporary subset of data for each user (to exist while the user is in database only) based on a date range the user selects from a form.

My initial thought is to have a command button on the form that runs a macro called getdata.

the macro would have command transferdatabse which would look like this:

Transfer type: Export
Database Type: Microsoft Access
Database Name: I:\SLF\SupChain\APSDATABASES\FP\data.mdb
Object Type: Query
Source: qryNationalCriteria
Destination:
Structure Only: No

I'm stuck on the Destination part - how do I make it a uniqe table name for each user (there will be about 18 users).

Secondly - how do I get Access to clear out the users data table when the close the form?

Of course, I don't even know if this will accomplish what I am needing or not. So if anyone has any ideas - regarding this or a better way to accomplish this - I am more than willing to listen.

Thanks!

 

[luceze]

You can create a table from a button click event after the user selects the dates. Then set the forms datasource to the new table. The basic example below should get you started.

DoCmd.SetWarnings False
strSQL = "SELECT YourTable.YourField INTO tbl" & Environ("username") & " FROM YourTable;"
DoCmd.RunSQL strSQL
DoCmd.OpenForm "YourFormName"
Forms!YourFormName.RecordSource = "tbl" & Environ("username")
DoCmd.SetWarnings True

If you need a specific example then provide specific information such as form, table and field names.

HTH,
Eric

 

[gwog]

Eric,

Thank-you for the response. I'm afraid I'm going to have to ask for some additional help though.

If I understand correctly - I would put the code behind the on click property of a command button on the form right?

I went to my query (it is a select query that simply pulls all data from my table based on the dates choosen on the form) and copied the SQL of it here:

SELECT [NATIONAL - ABS].SDATE, [NATIONAL - ABS].ITEM, [NATIONAL - ABS].[JDE ITEM], [NATIONAL - ABS].HISTQTY, [NATIONAL - ABS].HISTFCST, [NATIONAL - ABS].ABS, 1-([NATIONAL - ABS]![ABS]/[NATIONAL - ABS]![HISTQTY]) AS [FA%], [NATIONAL - ABS]![HISTQTY]/[NATIONAL - ABS]![HISTFCST] AS consum, [NATIONAL - ABS].[Stock Type], [NATIONAL - ABS].Description, [NATIONAL - ABS].Company, [NATIONAL - ABS].LOB, [NATIONAL - ABS].Category, [NATIONAL - ABS].[Sub Category], [NATIONAL - ABS].Allowance, [NATIONAL - ABS].[Channel CC], [NATIONAL - ABS].Brand, [NATIONAL - ABS].[Sub Brand], [NATIONAL - ABS].SCCAT, [NATIONAL - ABS].SUPPLYGROUP, [NATIONAL - ABS].[Promoted Group], [NATIONAL - ABS].ReasonCode, [NATIONAL - ABS].comments
FROM [NATIONAL - ABS]
WHERE ((([NATIONAL - ABS].SDATE) Between [Forms]![new]![ComboBsdate] And [Forms]![new]![ComboEsdate]));

My form name is New.

Based on the above information could you give me more specidfic how to plug it into the code sample?

The other question is where does the username come from? Do I need to add it to my table?

Thanks

Lisa

 

[luceze]

This will create your temporary table called tbl + the windows logon name.


DoCmd.SetWarnings False
strSQL = "SELECT [NATIONAL - ABS].SDATE, [NATIONAL - ABS].ITEM, " & _
"[NATIONAL - ABS].[JDE ITEM], [NATIONAL - ABS].HISTQTY, " & _
"[NATIONAL - ABS].HISTFCST, [NATIONAL - ABS].ABS, " & _
"1-([NATIONAL - ABS]![ABS]/[NATIONAL - ABS]![HISTQTY]) AS [FA%], " & _
"[NATIONAL - ABS]![HISTQTY]/[NATIONAL - ABS]![HISTFCST] AS consum, " & _
"[NATIONAL - ABS].[Stock Type], [NATIONAL - ABS].Description, " & _
"[NATIONAL - ABS].Company, [NATIONAL - ABS].LOB, [NATIONAL - ABS].Category, " & _
"[NATIONAL - ABS].[Sub Category], [NATIONAL - ABS].Allowance, " & _
"[NATIONAL - ABS].[Channel CC], [NATIONAL - ABS].Brand, " & _
"[NATIONAL - ABS].[Sub Brand], [NATIONAL - ABS].SCCAT, " & _
"[NATIONAL - ABS].SUPPLYGROUP, [NATIONAL - ABS].[Promoted Group], " & _
"[NATIONAL - ABS].ReasonCode, [NATIONAL - ABS].comments INTO tbl" & Environ("UserName") & " FROM " & _
"[NATIONAL - ABS] WHERE ((([NATIONAL - ABS].SDATE) Between " & _
"[Forms]![new]![ComboBsdate] And [Forms]![new]![ComboEsdate]));"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


To clear out the table when the form is closed add this code to the forms on close event.


DoCmd.SetWarnings False
strSQL = "DELETE tbl" & Environ("username") & ".* FROM tbl" & Environ("username") & ";"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


HTH,
Eric

 

[gwog]

Eric,

Thank-you so much!

I have a few more questions if you don't mind:

1) How do I use this new temporary table as the basis for everything else on my form?

2) Can this temporary table ("strsql") be used in a query?

I can give more specifics if you need them...

Thank-you so much for your help!

Lisa.

 

[luceze]

Is the form you need populated the same form in which you enter the date criteria or do you need to open a new form?

If you need to open a new form and set its record source to the newly created table then add this code to the end of the code posted above.

DoCmd.OpenForm "YourFormName"
Forms!YourFormName.RecordSource = "tbl" & Environ("username")

As to your second question, the SQL string (strSQL) that is posted above is a Make Table SQL command. Hence, it will only create a new table when the SQL is executed.

HTH,
Eric

 

[gwog]

Eric,

I really appreciate your quick response.

I'm afraid though that my form may be too complicated, for this process.

See my form has several components:

1) there is the date combo boxes
2) Once the temporary table is created based on the date - I need it to be so that the user can then query it farther based on several additional combo boxes.
3) Then two sub-forms are opened based on a grouplevel choosen.

So you see there is a lot involved - and I understand if it is way too much to ask for additional help (because I'd need alot of it).

The code you gave me is awesome though - and it will most definitely help me in the future.

I'm afraid that it seems I am just going to have to deal (and the users will too) with how slowly my current forms sub-forms load.

I do indeed appreciate your assistance!

Lisa.

 

[luceze]

Lisa,
What you will have to do is create a query based on the temp table. If only one user will be using the database from his or her workstation (I am assuming that you have it split to a frontend and backend), then a static query will work and you can add your parameters to this query. If several people are going to be using this database from a particular workstation then you will have to set the query's source table dynamically. Unfortunately I don't know how to do that at this moment, but I'll try to figure it out. Hopefully someone else will have an alternative solution. If you are having problems with your forms opening slowly then go to Tools/Options/General and uncheck the Log Name AutoCorrect Changes check box. There is a lengthy post about form speed here:

http://www.tek-tips.com/viewthread.cfm?SQID=206410&SPID=702&newpid=702&page=1

HTH,
Eric

 

[gwog]

Eric,

I'm using Access 97 - and the AutoCorrect Chnages check box doesn't exist from the Tools-Options-General tab. but thanks for the tip and the link to the post. I think I've already viewed it before - but I will look at it again to be sure I haven't missed trying anything.

As for my database. Each user access the front end database (which resides on a server) from their own PC.

Thanks for all you help.

Lisa.