Runtime error '91' after upsizing mdb

[Drew]

I am really new to this upsizing wizard, and now I'm in a bind. I upsized
an mdb to SQL Server 2000. It created the adp file, but when it is run it
gives me the following error,

Run-time error '91':

Object variable or With block variable not set

If I click on Debug, it highlights the following code in yellow,

Set rst = dbs.OpenRecordset(strSQL)

The rest of the code looks like this,

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set dbs = CurrentDb()
strSQL = "SELECT * FROM [Switchboard Items]"
strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
Me![SwitchboardID]
strSQL = strSQL & " ORDER BY [ItemNumber];"
Set rst = dbs.OpenRecordset(strSQL)

What is the deal here? It looks like it doesn't know where to go to find
the Switchboard Items, but I'm not sure how to tell it where to look.

Thanks,
Drew

 

[Jacek Segit]

Witaj Drew,
W Twoim li¶cie datowanym 17 czerwca 2004 (17:41:00) mo¿na przeczytaæ:

D> I am really new to this upsizing wizard, and now I'm in a bind. I upsized
D> an mdb to SQL Server 2000. It created the adp file, but when it is run it
D> gives me the following error,
D> Run-time error '91':
D> Object variable or With block variable not set

D> If I click on Debug, it highlights the following code in yellow,
D> Set rst = dbs.OpenRecordset(strSQL)
D> The rest of the code looks like this,
D> ' Open the table of Switchboard Items, and find
D> ' the first item for this Switchboard Page.
D> Set dbs = CurrentDb()

The is no longer CurrentDb. ADP uses CurrentProject.

D> strSQL = "SELECT * FROM [Switchboard Items]"
D> strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
D> Me![SwitchboardID]
D> strSQL = strSQL & " ORDER BY [ItemNumber];"
D> Set rst = dbs.OpenRecordset(strSQL)

D> What is the deal here? It looks like it doesn't know where to go to find
D> the Switchboard Items, but I'm not sure how to tell it where to look.


First of all - you use DAO. DAO is not recommanded (and not compatible?) with SQlServer.
My suggestion: change "every DAO" to ADO.
Look for ADODB.Command,ADODB.Recordset in your VBA help

Regards
Jacek Segit

 

[yanyping]

I am really new to this upsizing wizard, and now I'm in a bind. I upsized
an mdb to SQL Server 2000. It created the adp file, but when it is run it
gives me the following error,

Run-time error '91':

Object variable or With block variable not set

If I click on Debug, it highlights the following code in yellow,

Set rst = dbs.OpenRecordset(strSQL)

The rest of the code looks like this,

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set dbs = CurrentDb()
strSQL = "SELECT * FROM [Switchboard Items]"
strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
Me![SwitchboardID]
strSQL = strSQL & " ORDER BY [ItemNumber];"
Set rst = dbs.OpenRecordset(strSQL)

What is the deal here? It looks like it doesn't know where to go to find
the Switchboard Items, but I'm not sure how to tell it where to look.

Thanks,
Drew