October 15, 2011 at 7:13 am
Hi Guys,
Now that I have been working with SQL Server 2008 for a week now and being initially daunted by it I must admit I do like it, particularly the SSMS GUI !
I am now going down the path of using MS Access 2003 as a front end and linking talbes is out, the recommended way is ADODB, (not ODBC) I think.
Does anyone have a simple connection script to MSSQLSERVER2 instance name and FirstDatabase dbname that will run in MS Access 2003 please ?
I have tried copying numerous scripts from the internet without success.
kind regards to all you hard workers.
October 15, 2011 at 7:39 am
October 15, 2011 at 7:51 am
Here is a decent example of getting your connection established. http://support.microsoft.com/kb/306125
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 15, 2011 at 12:45 pm
Hi Guys,
That a good help, this is how far I have got tho from putting code togethermn, still not working !
Rem Open a connection without using a Data Source Name (DSN)
Dim db As ADODB Connection
Set Conn = New ADODB.Connection
Conn.ConnectionString = "Provider='SQOLEDB'';Data Source='MSSQLSERVER2'; Initial Catalog='FirstDatbase';Integrated Security='SSPI';"
Conn1.Open
MsgBox ("Conn state: " & GetState(Conn.State),,,)
REM Call a Stored Proc
Rem
Conn.Close
Set Cpnn = Nothing
Exit Sub
ErrorHandler:
Rem Clean up
If Not Conn Is Nothing Then
If Conn.State = adStateOpen Then Conn.Close
End If
Set Conn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
October 15, 2011 at 1:55 pm
Conn.ConnectionString = "Provider='SQOLEDB'';Data Source='MSSQLSERVER2'; Initial Catalog='FirstDatbase';Integrated Security='SSPI';"
Conn1.Open
3 things.
1. provider should be SQLOLEDB
2. Conn is your connection object, why are you writing conn1.open?
3. remove all single quotes from the connection string.
October 16, 2011 at 5:20 pm
Thanks, typed it in a hurry.
Will give it a go tomorrow and let you know !
Regards
October 17, 2011 at 8:40 am
Here's some generic VBA code to get you started using ADO in Access. It relies on a trusted connection to the database.
You will always need to first set a reference in the VBE (Tools, References) to "Microsoft ActiveX Data Objects x.x Library" where "x.x" is some version number. Just select the highest version available if there's more than one to pick from.
This example presumes you are trying to create a Recordset from a SQL stored procedure.
Replace the following with your own items: MyServer and MyDatabase with the Server & DB you are using, "MyStoredProcedure" w/ the SP, and the 2 parameters with whatever your SP requires for input parameters. You can declare and output parameter to catch a return value from the SP, but I didn't include that:
Dim con As ADODB.Connection, cmd As ADODB.Command, rs As ADODB.Recordset
' DB Connect and query
' ===============================================================================================
Set con = New ADODB.Connection
With con
.ConnectionString = "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI"
.CursorLocation = adUseClient ' Allows backward scrolling through generated recordset
.Open
End With
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = con
.CommandText = "MyStoredProcedure"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@StringParameter", adVarChar, adParamInput, 100, strEvalPeriod)
' N.B.: INT data types require a ZLS in the penultimate position in the list of arguments, after "adParamInput"
.Parameters.Append .CreateParameter("@INTParameter", adInteger, adParamInput, , TCID)
End With
Set rs = cmd.Execute
' Cleanup
' ===============================================================================================
rs.Close: Set rs = Nothing
Set cmd = Nothing
con.Close: Set con = Nothing
Good luck,
Rich
October 18, 2011 at 10:50 am
Thanks old hand.
Tried your code but without success. My sql server is loaded on my PC along with ma access.
I have no problems linking tables between SQL Server and Access or running pass-through queries using ODBC.
But my ADO code stops/times out at 'open', see my code below all advice appreciated.
Private Sub Command14_Click()
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
' DB Connect and query
' ===============================================================================================
Set con = New ADODB.Connection
With con
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "ADMIN_PC\MSSQLSERVER2"
'Also tried just MSSQLSERVER2 without success
.Properties("Integrated Security").Value = "SSPI"
.Properties("Initial Catalog").Value = "FirstDatabase"
.Open
'Stop here !
End With
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = con
.CommandText = "uspGetClient"
.CommandType = adCmdStoredProc
End With
Set rs = cmd.Execute
'To Display new values on a form
'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
' Cleanup
' ===============================================================================================
rs.Close: Set rs = Nothing
Set cmd = Nothing
con.Close: Set con = Nothing
End Sub
October 18, 2011 at 10:58 am
Can you please try connecting using the syntax I posted?
You have a line [.Provider = "Microsoft.Access.OLEDB.10.0"] in your code. If you are trying to connect to a SQL Server, this is incorrect.
Try using what I posted, making the necessary substitutions.
Rich
October 18, 2011 at 11:06 am
Hi,
Yes here is you original code and it stop/timesout at 'open' as well
Private Sub Command15_Click()
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
' DB Connect and query
' ===============================================================================================
Set con = New ADODB.Connection
With con
.ConnectionString = "Provider=SQLOLEDB;Data Source=LOCAL;Initial Catalog=FirstDatabase;Integrated Security=SSPI"
.CursorLocation = adUseClient ' Allows backward scrolling through generated recordset
.Open
End With
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = con
.CommandText = "uspGetClient"
.CommandType = adCmdStoredProc
'Set the Parameters
October 18, 2011 at 11:08 am
I have replaced LOCAL with MSSQLSERVER2
October 18, 2011 at 11:28 am
The magic just worked today.
Here is the working code, problem lay in the name of Source !
Thanks for help everyone, I go there in the end !
Private Sub Command15_Click()
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
' DB Connect and query
' ===============================================================================================
Set con = New ADODB.Connection
With con
.ConnectionString = "Provider=SQLOLEDB;Data Source=ADMIN-PC\MSSQLSERVER2;Initial Catalog=FirstDatabase;Integrated Security=SSPI"
.CursorLocation = adUseClient ' Allows backward scrolling through generated recordset
.Open
End With
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = con
.CommandText = "uspGetClient"
.CommandType = adCmdStoredProc
'Set the Parameters
' N.B.: INT data types require a ZLS in the penultimate position in the list of arguments, after "adParamInput"
' .Parameters.Append .CreateParameter("@StringParameter", adVarChar, adParamInput, 100, strEvalPeriod)
' .Parameters.Append .CreateParameter("@INTParameter", adInteger, adParamInput, , TCID)
' SET Vlaue of @IntParametes
' .Parameters("@IntParameter") = Me.txtBox
'End With
Set rs = cmd.Execute
'To Display new values on a form
'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
' Cleanup
' ===============================================================================================
rs.Close: Set rs = Nothing
Set cmd = Nothing
con.Close: Set con = Nothing
October 18, 2011 at 11:32 am
Glad you got there!
Yes, with named instances you must include the server\instance in the connection string.
Thanks for letting us know you were able to make it work,
Rich
October 18, 2011 at 3:48 pm
No thank you.
But now that I can view my tables, my next questions is how do I update my records !
I think I would do one at a time but what if you let the user see al the records ?
Also I think I read somewhere you can change a setting in the record set code to allow the record set to be editable?
But is that best practice?
Thanks again?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply