By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,781 Members | 1,134 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,781 IT Pros & Developers. It's quick & easy.

Access to Micosoft SQL - run time error 91

P: 2
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
Nov 22 '06 #1
Share this Question
Share on Google+
7 Replies


Expert 5K+
P: 8,434
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.
Nov 24 '06 #2

Expert 5K+
P: 8,434
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?
Nov 24 '06 #3

100+
P: 1,646
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
Nov 24 '06 #4

P: 2
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.
Nov 29 '06 #5

Expert 5K+
P: 8,434
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.
Nov 29 '06 #6

100+
P: 1,646
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.
Dec 1 '06 #7

P: 2
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
Jan 12 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.