?
Solved

Refresh

Posted on 2005-03-07
7
Medium Priority
?
501 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

621 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