Solved

VB, SQL Server, WINDOWS NT...Most Urgent...

Posted on 1998-07-23
3
164 Views
Last Modified: 2010-05-18
I have four different Questions.....

I have two machines...One As WIN-NT SERVER and Other as
WIN-95 client. I have installed the SQL Server 6.5 on the
WIN-NT SERVER.

I have installed the SQL Server Client on WIN-95 machine.
Visual Basic 5.0 - Enterprise edition is also installed on
WIN-95 machine.

Q-1 : How do i connect SQL server running on WIN-NT server
      in my Visual Basic Project ( WIN-95 machine ) ???

Q-2 : How do i open the databases & tables of SQL server  
      from the Visual Basic Project ( WIN-95 machine ) ???

Q-3 : How can i add / modify / delete records in SQL server
      tables ???

Q-4 : How do i query the SQL server tables... ???

I want the answer with detailed example. Ofcourse i do
agree that the points given here are very less...but that
is what i have with me...

Expecting the answers from you experts...

Few more information : WINDOWS-NT DOMAIN : TAPS_DOMAIN
                       SQL Server Name   : NT_SERVER

Kind Regards,

TAPS
0
Comment
Question by:tapsbin
  • 2
3 Comments
 
LVL 14

Accepted Solution

by:
waty earned 400 total points
Comment Utility
Q-1 : How do i connect SQL server running on WIN-NT server
      in my Visual Basic Project ( WIN-95 machine ) ???

You have to create a DSN to establish the connection with your SQL Server (see in your control panel)

In your code do the following (modify the user, pwd, dsn name in
funtion of your database)
Global Const gConnectionString = "ODBC;DATABASE=YourDB;UID=YourUser;PWD=YourPassword;DSN=Your DSNName"

' *** Open database using ODBC
Set DB = OpenDatabase("", dbDriverNoPrompt, False, gConnectionString)


Q-2 : How do i open the databases & tables of SQL server  
      from the Visual Basic Project ( WIN-95 machine ) ???
To establish the connection with the database, see the previous question.

Here is a way to read records in a table
Public Sub ReadInfosUSer()
   Dim sTmp          As String
   Dim record        As Recordset
   Dim sSQL          As String
   
   sSQL = "Select NomD, PrenomD, Titre, Etage, Bureau, Batiment, Fax, Print_Group "
   sSQL = sSQL & "From User "
   sSQL = sSQL & "Where User_Code = '" & gsUserCode & "'"
   
   Set record = DB.OpenRecordset(sSQL, dao.dbOpenForwardOnly, dao.dbSQLPassThrough)
   
   If (record.RecordCount <> 0) Then
      ' *** Infos on the user
     
      gsUserNom = ReadRecordSet(record, "NomD")
      gsUserPrenom = ReadRecordSet(record, "PrenomD")
      gsUserBatiment = ReadRecordSet(record, "Batiment")
      gsUserEtage = ReadRecordSet(record, "Etage")
      gsUserBureau = ReadRecordSet(record, "Bureau")
      gsUserPrintGroup = ReadRecordSet(record, "Print_Group")
      gsUserTitle = ReadRecordSet(record, "Titre")
      gsUserFax = ReadRecordSet(record, "Fax")
   
   End If
   
   record.Close
   Set record = Nothing

End Sub

Q-3 : How can i add / modify / delete records in SQL server
      tables ???
You can use query to add, modify, delete records

Dim sSQL          As String

sSQL = "Delete from YourTable Where (Condition > 50)"
DB.Execute sSQL, DAO.dbSQLPassThrough

sSQL = "Insert into YourTable Values(50)"
DB.Execute sSQL, DAO.dbSQLPassThrough

sSQL = "Update YourTable Set Name = 'TOTO' Where (Condition = 50)"
DB.Execute sSQL, DAO.dbSQLPassThrough

You can also modify, delete or add using a recordset, but it can not use the SQLPassThrough option.

   Dim sTmp          As String
   Dim record        As Recordset
   Dim sSQL          As String
   
   sSQL = "Select NomD, PrenomD, Titre, Etage, Bureau, Batiment, Fax, Print_Group "
   sSQL = sSQL & "From User "
   sSQL = sSQL & "Where User_Code = '" & gsUserCode & "'"
   
   Set record = DB.OpenRecordset(sSQL, dao.dbOpenDynaset)

   if record.EOF = false
      record.Edit
      ...
      record.Update
   end if
   record.Close
   Set record = Nothing


Q-4 : How do i query the SQL server tables... ???
See Question 2

If you need, I can send you a complete sample project wich query a SQL database for tables and generate some code for each table.

0
 

Author Comment

by:tapsbin
Comment Utility
Thanks,

let me check it out ...on my machines...i will come back to you
tomorrow...

So much thanks,

TAPS

0
 

Author Comment

by:tapsbin
Comment Utility
Dear waty,

I have defined the DSN ( Data Source Name ) through the Control
Panel....But in server Combo Box it is not displaying the
SQL Server name...even then i added ...

While running your code it gives the message as
Unable to find SQL server .....

Please help...

Please tell me what do i need to check ...to make sure that my
DSN entry is correct...

Kind Regards,

TAPS

0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now