Access 2007 Form - sqlserver2000

Hi everybody,

I am using Access 2007 and i am trying to connect to sql server 2000 from a form in access 2007. I havent done this before, after some search i tried this code, can you advise me if it is correct:


Dim connsql1 As String
connsql1 = "test"

Dim connection1 As SqlClient.SqlConnection

connection1 = SqlClient.SqlConnection("Data Source=ServerName;Initial Catalog=DatabaseName;User Id=sa;Password=sa")

Dim myCMD As SqlCommand
myCMD = "select count(n0_period_no) from plu"
Text1.Text = myCMD


First of all i want to get an input from the program that the connection succeded, that is why i run this query and i want to display the result in the textox...


Any ideas are really appreciated,

Thanks in advance,:wave:
Best Regards

i would recomend that you use ADO for this connection.

Search on here for ADO

and the connections string you need is

vb Code:

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

changing the name for Datasource,user, Database and password.

check out connectionStrings also

Dear All,

I finally managed to make a connection from Access 2007 form to Sql Server 2000. I am trying to run a simple query to see the result in a textbox, but i get the following error. In the SQL database the field n0_department_no is decimal (5 lenght).

The code is below any help is appreciated


Dim ad As adodb.Connection
Set ad = New adodb.Connection


Let ad.ConnectionString = "Driver={SQL Server}; Server=ServerName; Trusted_Connection=no; Database=DBName; UID=user ;PWD=pwd"
ad.Open

Dim ar As adodb.Recordset
Set ar = New adodb.Recordset

ar.Open "select n0_department_no from plu where sz_item_ref_no = 59091", ad, adOpenKeyset, adLockPessimistic

Text1.Text = ar!sz_item_ref_no


When i run this program i get the errror in the image, ANy ideas ????


Thanks in advance,

Best Regards

Which line gives you the error?