Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Using ADO to link SQL Server 7 and MovePrevious

Posted on 2000-04-12
Medium Priority
Last Modified: 2013-12-25
Dear Expert ;
Problem 1
 Assume that I have Customer Table:Cust_ID , Cust_Name. which created in SQL Server7.
And I want to use ADO to link with this table. Please explain me how to link  without Data Source Name.

Problem 2
If I want to use Moveprevious this table.. How to use...

Please answer me...

Question by:u3520384
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 43

Expert Comment

ID: 2707225
This should get you started, add a reference to ADO 2.1 (if you have it) or 2.0 if not.

Paste this into a command button click event for example and run it.

Set the database and server names to reflect your situation.

    Dim cnnSQL As New ADODB.Connection
    Dim rstSQL As New ADODB.Recordset
    cnnSQL.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<DatabaseName>;Data Source=<ServerName>"
    rstSQL.Open "SELECT * FROM CustomerTable", cnnSQL, adOpenDynamic, adLockOptimistic
    With rstSQL
        msgbox !CustID
        msgbox !CustID
        msgbox !CustID
    End With
    Set rstSQL = Nothing
    Set cnnSQL = Nothing

Expert Comment

ID: 2708046
dim DbDossier As ADODB.Connection
dim rsData As New ADODB.Recordset

Set DbDossier = New ADODB.Connection

DbDossier.Open "uid=sa;pwd=xxx;server=SQLSERVER;" & _
               "driver={SQL Server};database=NameOfDatabase;"

Set rsData = DbDossier.Execute("select * from CustomerTable"

msgbox rsData("Cust_Name")
' show the field Cust_Name

' go to the next record


where "uid" is the sqlusername
"pwd" is the password for the sqluser
"SQLSERVER" is the name of your sql server
"NameOfDatabase" is the name of the database containt the CustomerTable table

LVL 43

Expert Comment

ID: 2708123
saile01, I can see that you are relatively new to EE so please don't take my comments to heart.

The EE site has a set of guidelines, which are at http://www.experts-exchange.com/info/usrcntr.htm. In brief though it is considered bad practice to submit an "answer" unless you can guarantee that it will solve the questioner's problem. Especially where in this situation you have in fact posted a similar answer to the comment already posted.

To pick up a few points within your suggestion, there is a missing bracket on the Set rsData line, also I think you missed the point of the original questioner, he/she asked to be able to use the .MovePrevious method on the returned recordset. The example that you give will open the default ForwardOnly recordset which will not permit this method to be used.

I would suggest that u3520384 rejects your proposed answer at this stage. This will allow other experts perhaps to "comment" on this question.

Should u3520384 choose at a later time to accept your "comment" as an answer then he/she is at liberty to do that. It may be that he/she prefers my suggestion or that an altogether different one may be thrown up by another expert and chosen in preference to either of ours.

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Expert Comment

ID: 2709119
u3520384  to use move previous ,you should consider following things :

1.If you'r Recordset supports only scrolling forward only ,calling move first,move last,or move previous will generate a run-time error.So,make sure your cursor is not forward only,even if you specifed it as adOptimistic.

2.If you request a Client-side recordset and then check the cursor Type property,ADO will report that you are using a Static cursor.

3.Static cursor supports scrolling forward and backward.

4.Static cursors are not updatable in theory.But they can be updatable.If you need to update your recordset,let me know and I will show you how to update with static cursor.It is unclear form ur Question wether you wanna update or not.

5.see the following code where I am using move previous:

Mystring = "SELECT * From CustomerTable"
rsCustomers.Open Mystring,ConDatabase,&_

Do while Not RecStCustomers.BOF
 Debug.Print RecStCustomers.Fields ("CustomerName").Value


Expert Comment

ID: 2709139
I forgot to put Move previous method in the Do While loop.Here is the code again :

Mystring = "SELECT * FromCustomerTable"

RecstCustomers.OpenMystring,ConDatabase,&_ adOpenStatic

Do while Not RecStCustomers.BOF
 Debug.Print RecStCustomers.Fields("CustomerName").Value


Accepted Solution

MIrfan earned 600 total points
ID: 2718314

Dim ConString As String
  Dim MySQLString As String
  Dim MyConnection As ADODB.Connection
  Dim rsResults As ADODB.Recordset
 Set MyConnection = New ADODB.Connection
 Set rsResults = New ADODB.Recordset
'Build your connection string with out a DSN.
ConString = "Provider=SQLOLEDB;Data Source=YourServerName;" & _
"Initial Catalog=YourDatabaseName;User ID=YourID;Password=YourPassword;"
MySQLString = "select * from CustomerTable"

rsResults.CursorLocation = adUseClient
rsResults.CursorType = adOpenStatic
Set rsResults = MyConnection.Execute(MySQLString)

Problem 2:
By default the cursor will be pointing at the first Record.I am doing a check for Begining of File(BOF),which is recomended to do every time you use move Previous.So if you wanna use MovePrevious,use rsResult.MoveLast  first then use Move.Previous. Again read the comments I provide you earlier for using Move.Previous or MoveLast,it all depends on your Cursor.I hope this helps.

Do while Not rsResults.BOF
 Debug.Print rsResults.Fields("CustomerName").Value

LVL 43

Expert Comment

ID: 2734187

Please see my comments to Saile01 earlier in this thread, the answer that you have given is substantially similar to that already posted by myself.


Please could you decide what to do with this thread, whilst I am not hassling to get the points, I believe that I have given a valid answer to your question and without anything from you to the contrary then the fact that now two other "New" "Experts" have posted essentially the same thing is a little annoying.

Expert Comment

ID: 2735087
You can have the points TimCotte, my whole purpose was to help u352038.Everyone has a right to post there answers and comments ,you dont have to get annoyed.I dont think our answers are subtantially same.The connection string will be same for everyone who is accessing SQL server ,except for there UID and Pswd.Plus I am using a client-side cursor,where as you are using server side cursor,Iam using a static cursor,you are using a dynamic cursor.And most important you do not perform a BOF check which is very essential when you are using Move Previous.As I see it my answer is complete and solid.I dont wanna get into an argument ,I am just clarifying the statement you claimed earlier about experts are writing the same answer.The spirit of this site is to share knowledge and help out each other.You can have the points and have a nice day & relax.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question