Using ADO to link SQL Server 7 and MovePrevious

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...

Who is Participating?

Improve company productivity with a Business Account.Sign Up

MIrfanConnect With a Mentor Commented:

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

TimCotteeHead of Software ServicesCommented:
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
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

TimCotteeHead of Software ServicesCommented:
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 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.

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

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

TimCotteeHead of Software ServicesCommented:

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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.