Access and SQL database

  • Thread starter Thread starter jorge
  • Start date Start date
J

jorge

I have an Access database with a front-end and a back-end.
The database had grown big and the number of user in it
had increased. As a result of this, performance is very
poor.
My question is: Can I convert the date into SQL database
and still maintain my Access front-end to operate the
database? If so, Where can I find information to get
started on this.
 
Jorge

The best way to use SQL Server is with ADO. Start a new ADP (not an MDB,
which is more complicated) project. All your SQL Server compopnents are
there once you make the connection.

Joe
 
You could port the data over to SQL and then create a Link to the tables via
SQL / ODBC. The syntax in Projects(ADP) is a lot different that
MDB(Access), I have done this for reports. If you are using are using more
than reports then you should use Projects (ADP).

The Tables are still there, the Queries are now Views, and then there are
Stored Procedures(Very Powerful).

Your will be using a lot of SQL scripts and VBA code.

(Tip)
When running reports in ADP and you are passing parameters you will be using
Stored Procedures. the reference's go into the

Reference to passing parameters will be referenced in Input Parameters field
under the Report properties.
Reference to passing parameters for forms you will be using VBA for Forms.

Report::::
Example of a Stored Procedure that I created

Alter PROCEDURE [Booth Procedure]
@Select_Show int
As
SELECT shows.s_name, contracts.con_booth_assigned,
contracts.con_booth_sqft, exhibitors.e_name, shows.s_facility,
convert( int, rtrim(contracts.con_booth_assigned)) as BOOTH
FROM contracts INNER JOIN
exhibitors ON contracts.e_id = exhibitors.e_id INNER JOIN
shows ON contracts.s_id = shows.s_id
WHERE dbo.contracts.s_id = @Select_Show

Report (Main) Properties
Input Parameters @Select_Show = Forms![frm_SelectShowDialog]![SelectShow]

Form using a List Box

VBA Code:

Private Sub Last_Click()
' --- This resets the query for the last names ----
Dim FoundLastName As String

FoundLastName = Forms![frm_record_find].FindLastName
FoundLastName = "%" + FoundLastName + "%"

Me.List19.RowSource = "Exec FindLastName @LName = ' " &
FoundLastName & " ' "

Me.Repaint ' Save data for query
Me.Refresh ' Requery drop down listing
End Sub

Stored Procedure used:

Alter Procedure FindLastName
@LName as varchar(30)
As
SELECT DISTINCT
buyers.store_id as StoreID, buyers.buy_lname as Last,
buyers.buy_fname as First, stores.store_name as Store
FROM buyers INNER JOIN
stores ON buyers.store_id = stores.store_id
WHERE (buyers.buy_lname LIKE @LName)
ORDER BY buyers.buy_lname
return

I hope some of this help everyone.
 
Back
Top