I am using MS Access forms as a front end to a backend SQL Server
Database. I am trying to use an ADO connection to return a Select
Statement from the SQL Server to an Access form to be viewable by the
user. I have been able to establish the connection to the SQL Server
and have verified that the SQL statement is correct. I am completely
new to ADO and I can't figure out how to display the data returned in
the ADO recordset. Could someone please help me out with this? Is
there a way to display the returned recordset in an Access
form/datasheet to be viewable by the user? Below is my code for your
reference:
Option Compare Database
Option Explicit
Private Sub Form_Open(Cance l As Integer)
Dim Conn As Connection
Dim RS As Recordset
Dim LOC
Dim SQL
LOC = "PROVIDER=SQLOL EDB;DRIVER={SQL
Server};SERVER= MyServer;DATABA SE=MyDBase;UID= MyID;PWD=MyPWD"
SQL = _
"SELECT * From tblClaim"
Set Conn = CreateObject("A DODB.Connection ")
Set RS = CreateObject("A DODB.Recordset" )
Conn.Open LOC
RS.Open SQL, Conn, adOpenKeyset
Me.RecordSource = RS
RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing
End Sub 4 8984 as***********@g mail.com wrote:
I am using MS Access forms as a front end to a backend SQL Server
Database. I am trying to use an ADO connection to return a Select
Statement from the SQL Server to an Access form to be viewable by the
user.
Private Sub Form_Open(Cance l As Integer)
Dim c As ADODB.Connectio n
Dim m As ADODB.Command
Dim r As ADODB.Recordset
Set c = New ADODB.Connectio n
With c
.ConnectionStri ng = "PROVIDER=SQLOL EDB.1;" _
& "PERSIST SECURITY INFO=FALSE;" _
& "INITIAL CATALOG=Some Database;" _
& "DATA SOURCE=Some Data Source;" _
& "USER ID=Some User;" _
& "PASSWORD=S ome Password"
.CursorLocation = adUseClient
.Open
End With
Set m = New ADODB.Command
With m
.ActiveConnecti on = c
.CommandType = adCmdStoredProc
.CommandText = "spGet4060148Tr ansactions"
Set r = .Execute()
End With
With r
.Find "Transactio nID = 56"
If .EOF Then .MoveFirst
End With
Set Me.Recordset = r
End Sub
I actually ended up going a different direction with this...
Private Sub Form_Open(Cance l As Integer)
Dim c As ADODB.Connectio n
Dim r As ADODB.Recordset
Set c = New ADODB.Connectio n
With c
.ConnectionStri ng = "PROVIDER=SQLOL EDB;" & _
"DRIVER={SQ L Server};" & _
"SERVER=MyServe r;" & _
"DATABASE=MyDBa se;" & _
"UID=MyUID; " & _
"PWD=MyPwd"
.CursorLocation = adUseClient
.Open
End With
Set r = New ADODB.Recordset
With r
.ActiveConnecti on = c
.Source = "SELECT * From tblClaim"
.Open
End With
Set Me.Recordset = r
Text0.ControlSo urce = r.Fields("Prima ryKey").Name
End Sub
This seems to work as long as you create a form with unbound text boxes
then set the control source of the text boxes equal to the name of the
corresponding field in the recordset... as***********@g mail.com wrote:
I actually ended up going a different direction with this...
Private Sub Form_Open(Cance l As Integer)
Dim c As ADODB.Connectio n
Dim r As ADODB.Recordset
Set c = New ADODB.Connectio n
With c
.ConnectionStri ng = "PROVIDER=SQLOL EDB;" & _
"DRIVER={SQ L Server};" & _
"SERVER=MyServe r;" & _
"DATABASE=MyDBa se;" & _
"UID=MyUID; " & _
"PWD=MyPwd"
.CursorLocation = adUseClient
.Open
End With
Set r = New ADODB.Recordset
With r
.ActiveConnecti on = c
.Source = "SELECT * From tblClaim"
.Open
End With
Set Me.Recordset = r
Text0.ControlSo urce = r.Fields("Prima ryKey").Name
End Sub
This seems to work as long as you create a form with unbound text boxes
then set the control source of the text boxes equal to the name of the
corresponding field in the recordset...
It may be simpler to
1. connect to the sql db via an adp;
2. create the form with the sproc, table, sql string, view, whatever as
the recordsource; the wizard could be used (ugh!);
3. now all the controls have the desired fields as their control
sources;
4. remove the recordsource string;
5. use the form in the adp or import it to wherever;
6. add the form open code that set's the form's recordset to the AD)
recordset
I am at a similar stage to this. I can open a recordset and can loop
through it.
What I would also like to be able to do is to copy a table on the
server into a local table on the front-end. What is the simplest way
of achieving this?
Jim as***********@g mail.com wrote:
I actually ended up going a different direction with this...
Private Sub Form_Open(Cance l As Integer)
Dim c As ADODB.Connectio n
Dim r As ADODB.Recordset
Set c = New ADODB.Connectio n
With c
.ConnectionStri ng = "PROVIDER=SQLOL EDB;" & _
"DRIVER={SQ L Server};" & _
"SERVER=MyServe r;" & _
"DATABASE=MyDBa se;" & _
"UID=MyUID; " & _
"PWD=MyPwd"
.CursorLocation = adUseClient
.Open
End With
Set r = New ADODB.Recordset
With r
.ActiveConnecti on = c
.Source = "SELECT * From tblClaim"
.Open
End With
Set Me.Recordset = r
Text0.ControlSo urce = r.Fields("Prima ryKey").Name
End Sub
This seems to work as long as you create a form with unbound text boxes
then set the control source of the text boxes equal to the name of the
corresponding field in the recordset...
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Chris |
last post by:
Hi,
I have two, similar SQL Server 7.0 databases (in the same SQL Server
Group) that I use as the backend for two Access 2000 front end
applications - one is the live version, the other is the development
version.
The live version works fine. But the other version is read-only, i.e.
no changes can be made to the data in the tables, over...
|
by: Sham Yemul |
last post by:
Hello,
We developed an application that has many data entry forms and data
controls in Vb.net and Sql Server2000 as backend.
For this application we created connection object on load event of every
form. The application runs on Server machine with no errors.
We got some errors when the application/.exe run on client node, There is
no error...
|
by: OL |
last post by:
Hello All,
I need help understanding DB connection mgmt.
Scenario:
- 3 separate Web application
- IIS 5 or 6
- dynamic pages for most part
- DB Backend is Adaptive server Anywhere from sybase (max 10 concurrent
|
by: voipdealer |
last post by:
I have to interface to a third-party application that receives HTTP
POSTs with XML content to a certain port on the server. The server is
*not* a web server, rather some custom server app written by the
third-party, so it's behavior is not predictable or necessarily HTTP
compliant. Therefore, I can't use the much simpler HttpWebRequest....
|
by: Sharon |
last post by:
Hi all.
I'm trying first time async socket connection.
In all the examples i've seen, the server connection is closed
when the message is complete.
Is it common to close the connection after every message?
Also, the message is complete, meaning there is no more data to read, only
when the client closes the connection.
My solution is to keep...
| |
by: paulo |
last post by:
Hello,
I'm developing a web services apllication using SQL Server in mixed mode.
I'm also using WSE 3.0.
For each user in the application there's a corresponding user on the
database. The client always sends the username to the web service for
identification.
I have a connection string stored in the web service for the visual
designed...
|
by: mark_aok |
last post by:
Hi all,
I have a split database. Both the forms, and the tables are stored on
a shared network drive (this is Access 2003).
The users use the forms, and the tables on the network drive, there
are no local copies.
When connection to this drive is lost, Access CRASHES. It does it
every single time. Does anyone know if there is a way...
|
by: chris |
last post by:
Hi guys
I have a Frontend and Backend DB that works well on my LAN. I use a
INI file Path, to access a mapped network connection back to the
Backend Data file.
Path=F:\Server\Data.mdb
I have a situation now that requires a Remote Connection with my
|
by: =?Utf-8?B?Sm9obiBTdGFnZ3M=?= |
last post by:
Hello,
Please read this all before giving an answer :) I'm doing some
troubleshooting on a web application that my company wrote. It's written in
asp.net 1.1. The error that the Event viewer gives is:
Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |