migrating to SQL Server from Access

[dtwilder]

I used the upsizing wizard to migrate an Access db to SQL Server but am having some trouble with the .adp frontend. Specifically, I have one form that needs to find a particular table in the database (if it exists) and delete it. Here's the code:

Function fExistTable(strTableName As String) As Integer
Dim db As Database
Dim i As Integer

Set db = DBEngine.Workspaces(0).Databases(0)

'MsgBox "The db name is " & db.Name
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
'MsgBox "The object is named " & db.TableDefs(i).Name
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
'MsgBox "Table Found!"
Exit For
End If
Next i
Set db = Nothing
End Function

'Then, in the click event for a button, I have:
If fExistTable("tblPondLabInvertResults_frax") Then
DoCmd.RunSQL "DROP TABLE tblPondLabInvertResults_frax"
End If


Each time I run it, I get an error saying "Item not found in this collection". I'm pretty sure this is generated in the line "Set db = DBEngine.Workspaces(0).Databases(0)" in the function.

This problem has me worried that there'll be lots more migration problems for this db application.

Doug Wilder
National Park Service
Fairbanks, AK

[pclement]

I don't see a problem with the code. Are you certain it occurs on the following line?

Set db = DBEngine.Workspaces(0).Databases(0)

[dtwilder]

I'm pretty certain it happens in that line but I could be wrong.

I'm trying a new tactic, however. Instead of deleting and recreating a whole table, I'm trying to update a field in an existing table. I can still get where I need to but I need to have a stored procedure (which is the same sort of thing as an Access query, right?) update the value in the field of one table based on the value from another field in a different table. To do this I'm doing:

UPDATE dbo.tblResults
SET dbo.tblResults.CorrectedCount = dbo.tblResults.Count / dbo.tblSamples.FractionAnalyzed
FROM dbo.tblResults, dbo.tblSamples
WHERE dbo.tblResults.LabSampleID = dbo.tblSamples.LabSampleID

I need to divide the Count value by the FractionAnalyzed value and this is where my SQL statement seems to be failing. Any ideas?

Thanks much!
Doug in Fairbanks

[pclement]

What is the error you are getting?

[dtwilder]

I'm getting a new error message. What I'm doing now is this:

'Build SQL statement based on user input
'Query is a make-table query (needed because the user
'input defines a field to include in the results
<snip>

'First delete the table (tblqryInvertDataSummary)
'Must add code to check if this table exists
DoCmd.DeleteObject acTable, "tblqryInvertDataSummary"

'Execute the SQL statement
DoCmd.RunSQL stSQL

'Refresh db
DoCmd.DoMenuItem acFormBar, acEditMenu, acRefresh, acMenuVer20

'Open the new table
DoCmd.OpenTable "tblqryInvertDataSummary", acViewNormal, acReadOnly



When this runs, I get an error message saying "Microsoft Office Access can't find the object tblqryInvertDataSummary". The SQL is running fine but the table won't open. Any ideas?

Thanks!
Doug

[pclement]

Do you see the new table in the table list in Access?

[dtwilder]

Yes, the new table is created and contains the results I expect. I have to refresh the database (View-Refresh) before I see the table in the list. I'm just having trouble getting it to automatically open.

[pclement]

So if you run DoCmd.OpenTable again does it work OK? I'm just trying to figure out whether this is a timing issue and the table isn't immediately available after creating it.

[dtwilder]

No, it does not work if I add another line of DoCmd.OpenTable. It does seem like a timing thing. If I comment out the Refresh line, I get the same "can't find the object" error but the table is there and contains the results from the query. Seems like I need to have the program pause a bit and then open the table.

[pclement]

Actually I was asking if you could run it now, not whether it would work if you added another line.

[dtwilder]

Yes, it works fine if I have another button on the form to click to view the results. I'd rather not have to do that but I suppose it's a fix. So the query executes (creating a table) and then the user has to click another button to see the query results.

[pclement]

About the only thing I can think of would be to add a momentary delay and/or retry to see if you can work around the problem. It seems somewhat odd that the table wouldn't be immediately available after it has been created.

[dtwilder]

I added in a message box telling the user what search criteria they entered. After they click the OK button, the table opens. The message box fixed it but adds one more click the user must execute. I consider this victory. Thanks.

[pclement]

If you're interested, there are a few different code methods to simulate a delay or pause (http://tinyurl.com/7uhgo).

[JBourgeois]

CurrentDB is used in DAO.

Since an ADP project is a direct connection to SQL Server that does not use the JET Database engine, the code needs to be in ADO.

The upgrade wizard upgrades the tables, does what it can with the queries, but does nothing about the code which has to be rewritten with ADO.