Make-Table Query: Choose table name? 1

[SMHSleepy]

Is there a way to call upon a make-table query and be able to enter the name of the table you want to create?

 

[Andrzejek]

Have you tried:

http://tinyurl.com/68f24md

Have fun.

---- Andy

 

[SMHSleepy]

Hilarious! Thanks for the laugh.

Seriously though, yes I tried a google search and couldn't find anything. I know how to create a make-table query but you have to enter a table name up front. I made a simple form which allows the user to enter a date range and calls on the make-table query to create a new table within that date range. Now, I'd like to be able to run that query again with a different date range and have it create a new table with a different name.

Sorry if I didn't explain myself more clearly at first.

 

[Andrzejek]

How many tables are your users will be able to create while in your app? IMHO, it is not a good idea to allow users to create tables in your data base, and it is a nighmare to code agaist it.

If you explain *why* you need to have them created, it may be some easy way to do it (one temporary table? Predifined fields, known name) instead of creating new table(s) 'on-the-fly'

Have fun.

---- Andy

 

[MajP]

You can do this very simply. Something by building a sql string and the table name is a variable

Public Sub dynamicmakeTable()
  Dim strSql As String
  Dim tblName As String
  
  tblName = InputBox("Input table name", "Table Name")
  strSql = "SELECT LinkedTable.ProductID, LinkedTable.ProductName "
  strSql = strSql & " INTO " & tblName & " FROM LinkedTable"
  CurrentDb.Execute strSql
End Sub

However, Like Andy said this sounds like a horrible idea. Why would you need table unless they are archives "snapshots" for historical purposes. I would think hard.

 

[SMHSleepy]

Thanks MajP, That's exactly what I needed and I'll save it for another project. You and Andy made me realize I may be barking up the wrong tree. I wanted "snapshots" of payroll periods, which I though should be kept as separate tables but I don't actually need the data in a table, just a visual record.

I have reorganized the structure by using a form with unbound "start" and "end" date fields. I also made a lookup query to select records within these dates. Then a command button on my main form generates a report with my query as the control. I could then just export the report into a snapshot format to keep a visual record. These visual records are in case employees want to look back at past pay periods and verify their work hours.

Now, I have another issue/problem which I'd like to solve. Perhaps you could help?

I've created a data entry form for payroll. Fields in the table are simply: ID(autonumber), WorkDate, EmployeeName, and ShiftType. I have a drop down menu for ShiftType(D=day shift, EN=overnight shift, etc.). The EN is supposed to be split into E=Evening and N=Night, with N being dated for the next day. My code checks for EN, changes it to E, then adds a new record with WorkDate = WorkDate + 1 and ShiftType = N. Works like a charm but if I need to delete the overnight shift for some reason, I have to remember to delete both E and N records individually. Can you tell me how to code it so that if I delete either the E or N record, it will automatically delete the corresponding E or N record? TIA!