?
Solved

Refresh

Posted on 2005-03-07
7
Medium Priority
?
484 Views
Last Modified: 2006-11-17
I have the following code. After the sql run, I need to REFRESH the database so that the following code could see the table.

I try putting me.refresh  and justh refresh...it give me compile error.

Any ideas???

Function AlterTableSortId()
''Add sortId field to tblData

    Dim dbs As Database
    Dim myDate As String

       

Set dbs = OpenDatabase("Production Planning.mdb")
   
myDate = (InputBox("Enter Sticking Date", "Enter Date"))

    CurrentDb.Execute " SELECT tblStkDat.ISNo, tblStkDat.ItemNo, tblStkDat.Size, " _
    & " tblUniqueItemNoPlantName.FirstOfPlantName, tblStkDat.DayStick, " _
    & " tblStkDat.FltStick, tblBench.Bench, tblBench.BQty, tblStkDat.DatSowd, " _
    & " tblUniqueItemNoPlantName.Genus, IIf([Genus]=""THYMUS"",(([Size]*[Bqty])*2),([Size]*[Bqty])) AS TotalCut, " _
    & " CStr(Int(([TotalCut]*4/60/60/2)))+"":""+Right(""00"" & CStr(Int((([TotalCut]*4/60/60/2)-Int(([TotalCut]*4/60/60/2)))*60+0.5)),2) AS TotalTime " _
    & " INTO tblData " _
    & " FROM tblUniqueItemNoPlantName INNER JOIN (tblStkDat INNER JOIN tblBench ON " _
    & " (tblStkDat.ISNo = tblBench.ItemNum) AND (tblStkDat.DayStick = tblBench.DayStick)) " _
    & " ON tblUniqueItemNoPlantName.ItemNo = tblStkDat.ItemNo " _
    & " WHERE (((tblStkDat.DayStick)= #" & CDate([myDate]) & "# )); "
   
'''Refresh  >>>>>>Need refresh here.

    dbs.Execute "ALTER TABLE tblData " _
        & "ADD COLUMN SortId Long Integer;"
       
    dbs.Close
   
End Function

Thanks

perennial
0
Comment
Question by:perennial
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 800 total points
ID: 13480376
you need to refresh the tabledefs collection ...

dbs.TableDefs.Refresh

Steve
0
 
LVL 1

Author Comment

by:perennial
ID: 13480426
Still giving me run-time error:

Cannot find table or constraint.

It not seeing the newly create table.

I have to hit F5 after the module run to see the table... is there a way to do that?

perennial
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13480598
OK - you are executing the query using CurrentDb, but your target database is dbs. Try replacing the first execute line with:

    dbs.Execute " SELECT tblStkDat.ISNo, tblStkDat.ItemNo, tblStkDat.Size, " _
    & " tblUniqueItemNoPlantName.FirstOfPlantName, tblStkDat.DayStick, " _
    & " tblStkDat.FltStick, tblBench.Bench, tblBench.BQty, tblStkDat.DatSowd, " _
    & " tblUniqueItemNoPlantName.Genus, IIf([Genus]=""THYMUS"",(([Size]*[Bqty])*2),([Size]*[Bqty])) AS TotalCut, " _
    & " CStr(Int(([TotalCut]*4/60/60/2)))+"":""+Right(""00"" & CStr(Int((([TotalCut]*4/60/60/2)-Int(([TotalCut]*4/60/60/2)))*60+0.5)),2) AS TotalTime " _
    & " INTO tblData " _
    & " FROM tblUniqueItemNoPlantName INNER JOIN (tblStkDat INNER JOIN tblBench ON " _
    & " (tblStkDat.ISNo = tblBench.ItemNum) AND (tblStkDat.DayStick = tblBench.DayStick)) " _
    & " ON tblUniqueItemNoPlantName.ItemNo = tblStkDat.ItemNo " _
    & " WHERE (((tblStkDat.DayStick)= #" & CDate([myDate]) & "# )); "
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13480606
I meant to say - you are executing the *first* query using CurrentDb.
0
 
LVL 1

Author Comment

by:perennial
ID: 13480674
shanesuebsahakarn,

OK, this seem to work but where did it put my table?  When I go to the table it's not showing, I try to run the module again...it say that the table alreay exist.

perennial
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 1200 total points
ID: 13480701
It should be in the Production Planning.mdb.

If you want the tables in the current database, omit the Set dbs... line and just replace all instances of dbs with CurrentDB.
0
 
LVL 1

Author Comment

by:perennial
ID: 13480758
Thanks, that did it.

perennial
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question