help with changing some code from mdb to adp

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I got handed a little project where a person left something hanging…



The code that is failing is



Dim adoConn2 As New ADODB.Connection

Dim RS2 As New ADODB.Recordset

Dim strConn2 As String

strConn2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

strConn2 = strConn2 & "Z:\Brian Zorzi\Student DB.mdb"

adoConn2.ConnectionString = strConn2

adoConn2.Open

RS2.Source = " SELECT * FROM IEF WHERE [Company Name] Like " & "'%" &
strCompany & "%'" & "ORDER BY email;"

RS2.CursorType = adOpenForwardOnly

RS2.ActiveConnection = adoConn2

RS2.Open



The problem is Z:\Brian Zorzi\Student DB.mdb does not exsist any more it is
an adp file now connecting to a server, lets call it “sqlserv1â€



So how do I change this code around to work with “sqlserv1�



Please help me I am not savy with this code at all if you need more code to
see what is going on let me know.
 
So, now that Student DB .mdb is a Sql Server database...
So, you can use the code to connect to Sql server using
VB... Lot of information on the net about that....

This is how connections are done using ADP.

Dim sqlUsername As String
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cnn.Open CurrentProject.Connection (This is to connect to
your current db).
sqlUsername = 'Your Sql Statement here

rs.Open sqlUsername, cnn, 3, 3

If rs.EOF Then
MsgBox "Invalid User"
Exit Sub
End If
rs.Close
Set rs = Nothing

Hope this helps....

You can also use System DSN connection.

strConnSQL = "DSN=test;uid=test;pwd=test"
Dim myDSN as ADODB.Connection
myDSN.CursorLocation = 3
myDSN.Open strConnSQL

uid and password is the sql login info.

or you can use the DSNless connection to Sql server
through VBA... I am not sure about the syntax for that
but lot of info is available on the net.
 
At the end of this post is an example of an ADO connection string to a SQL
Server data source. You'll need to change 'pubs' to the name of your SQL
Server database, and '(local)' to the UNC path/name of your instance of SQL
Server. If you're not using integrated security, you may also need to pass a
user name and password.

I realise this may seem to leave a lot of questions unanswered if you're new
to this stuff, but they are questions that I can't answer for you - there's
no way for me to know, for example, what your instance of SQL Server is
called or whether you're using integrated security or not.

A useful tool is the Microsoft Data Link Properties dialog box, which can be
used to create or edit Univeral Data Link (UDL) files. In recent versions of
Windows, you can't create a new UDL file directly, but you can create a new,
empty text file and then simply change the file extension from .TXT to .UDL.
Double clicking the file will then open the Data Link Properties dialog box,
where you can browse for data sources and set properties in a more
user-friendly, graphical point-and-click manner than trying to write
connection strings manually. Once you have a connection set up correctly
using the dialog, you can open the UDL file in Notepad (it's plain text) and
copy the connection string and paste it into your code.

Here's the example I promised ...

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=pubs;Data Source=(local)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Ed said:
I got handed a little project where a person left something hanging.



The code that is failing is



Dim adoConn2 As New ADODB.Connection

Dim RS2 As New ADODB.Recordset

Dim strConn2 As String

strConn2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

strConn2 = strConn2 & "Z:\Brian Zorzi\Student DB.mdb"

adoConn2.ConnectionString = strConn2

adoConn2.Open

RS2.Source = " SELECT * FROM IEF WHERE [Company Name] Like " & "'%" &
strCompany & "%'" & "ORDER BY email;"

RS2.CursorType = adOpenForwardOnly

RS2.ActiveConnection = adoConn2

RS2.Open



The problem is Z:\Brian Zorzi\Student DB.mdb does not exsist any more it is
an adp file now connecting to a server, lets call it "sqlserv1"



So how do I change this code around to work with "sqlserv1"?



Please help me I am not savy with this code at all if you need more code to
see what is going on let me know.
Click to expand...
 
Back
Top