SQL Server Queries!!
-
How can I convert Microsoft Access queries to SQL Server and store it in the stored procedure?
How to open Sql Server database using Visual Basic code(ADODB connection)?
Thanks...
-
If you open an Access project (.adp), it will automatically convert your queries to either stored procedures or views. You can then edit or revise the stored procedure from SQL if desired.
Access writes stored procedures that are usable but less than ideal for maintenance.
If you are using ADP, Access will connect you to the SQL server DB automatically without ADO code.
Most MS Access users prefer .mdb, however, for many reasons. Nevertheless, you can get the ADP to write stored procedures for you if you are new and getting started with these.
HTH,
Sam
-
Thanks a lot Sam!
However, as you said, we are using the .mdb format. Is it possible to connect to SQL Server DB using ADO? Will it be tedious to make such conversion?
Thanks...
-
ling: If you're just getting started, I'd recommend Mary Chipman's book. You will find it extremely helpful if you are using SQL backend with MS Access frontend:
Here are a couple of examples to get you started connecting your .mdb via ADO code:
Public Function YourFunction() As Long
Dim cmdNew As ADODB.Command
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open fstrCnn()
Set cmdNew = New ADODB.Command
With cmdNew
Set .ActiveConnection = cnn
.CommandText = "proc_YourStoredProc"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@EmpCreated", adVarChar, adParamInput, 10, GetUserName())
.Parameters.Append .CreateParameter("@AnotherVariable", adInteger, adParamInput, Me.AnotherVariable)
.Parameters.Append .CreateParameter("@OutputParameter", adInteger, adParamOutput)
.Execute
Your Function = .Parameters("@OutputParameter").Value
End With
cnn.Close
Set cnn = Nothing
End Function
Private Sub InsertRecord()
Dim Conn As ADODB.Connection
Dim cmd As ADODB.Command
Set Conn = New ADODB.Connection
Conn.Open fstrCnn()
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = Conn
.CommandText = "proc_Insert " & ClientID & ", '" & fstrGetUserName() & "'"
.CommandType = adCmdText
.Execute
End With
Conn.Close
Set Conn = Nothing
End Sub
Note: fstrCnn() is set elsewhere. If you need to define your connection string here then:
Dim strCnn as string
strCnn= "Provider=sqloledb;Data Source=YourServer;Initial catalog=YourDB;Integrated Security=SSPI"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply