SQL Server Questions, PLEASE HELP ME!!!

[ind]

I have upsized by current database to SQL Server. Everything is working great except one thing. I have a form listed all the job that have been shipped but not invoiced. The form has a listbox (multi-select &quot;simple&quot and a command button. The user can select multiple job anc click the command button to change to status of the job to invoiced. My code works fine in Jet but not with SQL Server. What's the problem? Here's my code:<br><br>Private Sub CmdInvoice_Click()<br>Dim db As Database, rst As Recordset<br>Dim varNumber As Variant<br><br>If Me.lstJobsNotInvoiced.ItemsSelected.Count = 0 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;You must select at least one job to update.&quot;, vbOKOnly + vbInformation, &quot;No Jobs Selected&quot;<br>&nbsp;&nbsp;End If<br>Set db = CurrentDb<br>Set rst = db.OpenRecordset(&quot;tblJobs&quot;, dbOpenTable)<br><br>'Set Loop<br><br>For Each varNumber In Me.lstJobsNotInvoiced.ItemsSelected<br>rst.Seek &quot;=&quot;, lstJobsNotInvoiced.ItemData(varNumber)<br>rst.Edit<br>rst!strInvoiced = -1<br>rst.Update<br>Next<br>Me.lstJobsNotInvoiced.Requery<br><br>End Sub<br><br>I'm getting the error &quot;invalid operation&quot; on the following line of code:<br>Set rst = db.OpenRecordset(&quot;tblJobs&quot;, dbOpenTable)<br><br>Please can someone help????????<br>Thanks to all<br>

 

[cmmrfrds]

I think the syntax you are using for the query, Access considers DAO by default. If that is the case go under tool and select references. You should have &quot;Microsoft DAO 3.6.....&quot; selected or one the DAO libraries. I assume this is access 2000. Here is an example of code using the DAO library.<br><br>Function Testsomething()<br><br>Dim SqlString As String<br>Dim RSMT As DAO.Recordset, dbs As DAO.Database<br><br>Set dbs = CurrentDb<br>SqlString = &quot;UPDATE Equipment &quot; _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& &quot;SET equipmentName = &quot; & &quot;&quot;&quot;Samuel Adams&quot;&quot;&quot; _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& &quot; WHERE equipmentKey = 1 ;&quot;<br>dbs.Execute SqlString<br><br>End Function<br><br>Hope this helps.<br>Jerry

 

[jimmythegeek]

You should not use DAO with SQL Server. You need to use ADO. You need to get a book on ADO, because it is slightly different then DAO in the connection process.<br><br>Like you have to create a connection to your server/database.<br>Instead of Dim rs As Recordset, you will use Dim rs as ADODB.Recordset.<br>And a few other differences, like T-SQL is a little different than Access SQL. Like:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Access&nbsp;&nbsp;&nbsp;&nbsp;SQL Server<br>--------------------------------------------------------<br>Wildcard Character&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;*&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;%<br>Concatenation Char.&nbsp;&nbsp;&nbsp;&nbsp;&&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;+<br>String container&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot; &quot;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' '<br><br><br>Anyway, the normal Access methods for using recordsets is not going to work with SQL Server. There are many books available for ADO, and this is defenitely what you should be using.<br>&nbsp;<br>If have some samples somewhere, I will see if I can find them.<br><br><br><br><br> <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[jimmythegeek]

You should not use DAO with SQL Server. You need to use ADO. You need to get a book on ADO, because it is slightly different then DAO in the connection process.<br><br>Like you have to create a connection to your server/database.<br>Instead of Dim rs As Recordset, you will use Dim rs as ADODB.Recordset.<br>And a few other differences, like T-SQL is a little different than Access SQL. Like:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Access&nbsp;&nbsp;&nbsp;&nbsp;SQL Server<br>--------------------------------------------------------<br>Wildcard Character&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;*&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;%<br>Concatenation Char.&nbsp;&nbsp;&nbsp;&nbsp;&&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;+<br>String container&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot; &quot;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' '<br><br><br>Anyway, the normal Access methods for using recordsets is not going to work with SQL Server. There are many books available for ADO, and this is defenitely what you should be using.<br>&nbsp;<br>I have some samples somewhere, I will see if I can find them. <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[cmmrfrds]

Things have probably changed over the last couple years. My first Access application was using Sybase Sql Server about 4 years ago. I linked the tables much like Ind is probably doing and I used the same syntax he is using. The reason I did this was while developing the application I didn't need to be connected to Sybase. After finishing I used DBArtisan to move the tables and data to Sybase and then just linked the tables in the application which at the time worked fine. I wrote a lot of Transact-Sql and stayed away from Jet extentions in order to control how and what data would be returned to the application from Sybase&nbsp;&nbsp;-- Jet can require a lot of unnecessary data to the client. I understand that ADO is Microsofts direction for the future and I would use ADO when writing a new application, but one has to deal with maintenance where the client/user may not want to pay the immediate cost of converting the code.<br><br>On the ODBC connection to Sybase, I found that using the Sybase ODBC driver was significantly better for performance and one could eliminate problems that were endemic with the Microsoft ODBC driver. Of course, this is dated and may have changed.<br><br>I was going to convert another existing application to ADO but I was using RecordsetClones in my logic and would have needed to make significant logic changes in order to convert that part of the application. I chose instead to use DAO where I was using the RecordSetClone and ADO for the other calls.

 

[ind]

Can someone get me an example of an ADO connection to my server you my code. I am in sort of a hurry on this project.<br>Thanks again

 

[jimmythegeek]

First, put these in a public module: (change bolded area)<br><br>Public adoCN As ADODB.Connection<br>Public Const ADOConnect As String = &quot;Provider=SQLOLEDB.1;Integrated<br>Security=SSPI;Persist Security Info=False;Initial Catalog=Reporting;Data Source=<b>ServerNameHere</b>&quot;<br><br>Here is an ADO example:<br>Note: The two lines in red below, should really go on the OnOpen event of your switchboard, or opening form. This way, you can set the connection when your app opens, thus refer to it throughout the application. You could also set the connection every time you use it, but it is not necessary.<br><br>-----<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>Private Function GetCount(TableName As String, WhereStmt As String) As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim sCount As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rsCount As ADODB.Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;sCount = &quot;SELECT Count(*) AS Total FROM &quot; & TableName & &quot; WHERE &quot; & WhereStmt<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;<font color=red><b>Set adoCN = New ADODB.Connection<br>&nbsp;&nbsp;&nbsp;&nbsp;adoCN.Open (ADOConnect)</font></b><br><br>&nbsp;&nbsp;&nbsp;&nbsp;Set rsCount = New ADODB.Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;rsCount.Open sCount, adoCN, adOpenForwardOnly, adLockReadOnly, adCmdText<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;GetCount = rsCount!Total<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;rsCount.CLOSE<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rsCount = Nothing<br>End Function<br><br>-----<br><br>Make sure you have a reference to the Microsoft ActiveX Data Object 2.0 Or 2.1 Library. In Design view of a module, go to Tools ¦ References.<br> <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[ind]

Jim,<br>I've tried everything, how about using my code listed and converting it to ADO version for me. I would appreciated it a lot. I only want to update the choices that the user selects from the listbox.<br><br>Thanks so much

 

[jimmythegeek]

Replace the bolded areas with the appropriate information<br><br>Put these in a public module:<br><br>Public adoCN As ADODB.Connection<br>Public Const ADOConnect As String = &quot;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Reporting;Data Source=<b>ServerNameHere</b>&quot;<br><br><br>Put this on your Button click event:<br><br>==========&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>Private Sub CmdInvoice_Click()<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rst As ADODB.Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strSQL As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim varNumber As Variant<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set adoCN = New ADODB.Connection&nbsp;&nbsp;&nbsp;&nbsp;' Normally on initial open of application<br>&nbsp;&nbsp;&nbsp;&nbsp;adoCN.Open (ADOConnect)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Normally on initial open of application<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If Me.lstJobsNotInvoiced.ItemsSelected.Count = 0 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;You must select at least one job to update.&quot;, vbOKOnly + vbInformation, &quot;No Jobs Selected&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;For Each varNumber In Me.lstJobsNotInvoiced.ItemsSelected<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strSQL = &quot;UPDATE tblJobs SET strInvoiced = - 1 WHERE <b>FieldName</b> = &quot; & Me.lstJobsNotInvoiced.ItemData(varNumber)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;adoCN.Execute strSQL<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;Me.lstJobsNotInvoiced.Requery<br><br>&nbsp;&nbsp;&nbsp;&nbsp;rst.CLOSE<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rst = Nothing<br>End Sub<br>==========<br><br>The Fieldname would be like invoice number or something like that. Whatever it is in your listbox that you are trying to seek for.<br><br>This uses the Execute method to run an update SQL, rather than opening a recordset.<br> <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development