Access to Micosoft SQL - run time error 91

[cbxsims]

Hi,

I recently upsized my access database to ms-sql backend but some of my vb code is not working anymore. Please help. Here is my code. It was for a simple form with combo box's to run a query.

Option Compare Database
Option Explicit

Private Sub Command5_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("Stored1")
strSQL = "SELECT [CEO-NCO Log].* " & _
"FROM [CEO-NCO Log] " & _
"WHERE [CEO-NCO Log].[Assigned To:]='" & Me.cboName.Value & "' " & _
"AND [CEO-NCO_Log].[File Type] ='" & Me.cboFile.Value & "' " & _
"ORDER BY [CEO-NCO Log].[Assigned To:]"
qdf.SQL = strSQL
DoCmd.OpenQuery "Stored1"

Set qdf = Nothing
Set db = Nothing


End Sub

[Killer42]

I recently upsized my access database to ms-sql backend but some of my vb code is not working anymore. Please help. Here is my code. It was for a simple form with combo box's to run a query. ...

Could you be more explicit than "not working"? What is going wrong? If you're getting an error, please describe the circumstances, and the exact wording of the message. And if possible, which line of code produces it.

Oh, and what version of VB? Or is it VBA in Access, or what?

Oops! Just spotted the DoCmd - I guess it's Access VBA.

[Killer42]

strSQL = "SELECT [CEO-NCO Log].* " & _
"FROM [CEO-NCO Log] " & _
"WHERE [CEO-NCO Log].[Assigned To:]='" & Me.cboName.Value & "' " & _
"AND [CEO-NCO_Log].[File Type] ='" & Me.cboFile.Value & "' " & _
"ORDER BY [CEO-NCO Log].[Assigned To:]"

I admit, this is probably just my Access ignorance speaking - but could those colons be a problem? And do you need a semicolon on the end of the SQL string?

[willakawill]

Hi,

I recently upsized my access database to ms-sql backend but some of my vb code is not working anymore. Please help. Here is my code. It was for a simple form with combo box's to run a query.

Option Compare Database
Option Explicit

Private Sub Command5_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("Stored1")
strSQL = "SELECT [CEO-NCO Log].* " & _
"FROM [CEO-NCO Log] " & _
"WHERE [CEO-NCO Log].[Assigned To:]='" & Me.cboName.Value & "' " & _
"AND [CEO-NCO_Log].[File Type] ='" & Me.cboFile.Value & "' " & _
"ORDER BY [CEO-NCO Log].[Assigned To:]"
qdf.SQL = strSQL
DoCmd.OpenQuery "Stored1"

Set qdf = Nothing
Set db = Nothing


End Sub

Hi. Your vb code does not know what database to connect to. Creating a database object does not connect you to sql server. You need a connection string

[cbxsims]

It doesn't like this line,

Set qdf = db.QueryDefs("Stored1")

I get the errror run time error 91

Object variable or with block not set


Thanks for your responses.

[Killer42]

It doesn't like this line,
Set qdf = db.QueryDefs("Stored1")
I get the errror run time error 91
Object variable or with block not set
Thanks for your responses.

Hm... that sounds as though db hasn't been properly set. Maybe it's something to do with connecting to the database, as willakawill pointed out.

[willakawill]

It doesn't like this line,

Set qdf = db.QueryDefs("Stored1")

I get the errror run time error 91

Object variable or with block not set


Thanks for your responses.

You have to explicity connect to sql server. CurrentDb does not do this.

[stobenhaf]

I'm getting the famous error 91, but in a way I've never seen. I built a form to handle our work order system via SQL and included it in a project I had already started. The compiled project works fine on my development machine and the laptop with VB6 enterprise on it, but when I attempt to use it on a machine that doesn't have VB6 installed, it doesn't work. Everything else works except for the SQL form. Am I missing a DLL or some necessary file?

When the form comes up it's supposed to display the first record, but on the other machines the fields are just blank. As soon as I hit the new record, save record, or delete record button, I get the ERR 91 message. Like I said, no such problems on the machines with VB6.

I need to get this added soon.

Thanks