Data retrieval to Access 2007 from SQL with user input

[mutley1]

Hi Guys,

totally new to this so please don't send a "let me google that for you link". I am a SQL DBA but never used access before and have a simple question. I am looking for a web site with quick lessons on data retrieval using access. I have linked Access to my SQL table and want a simple form / report / whatever to pull back data based on user input. E.G I have an area code list and want to get the user to key in the 3 digit code IN ACCESS and the results of cities with that dial code to be returned.

I have tried googling with loads of different phrases but cant find anything specific. I just want a simple form with "Enter area code here", user to enter code, hit OK button and the 3 digit code to be passed as the "where areacode = xxx" part of the SQL query, then return said results.

Just a URL would do to read up on how to do it!

TIA,
M

 

[Savil]

Ideally you would have to do this in VBA using dynamic SQL.
You can have an Access pre-saved ODBC query holding all the connection values to the SQL database and change the SQL statement for the query on the fly. I think I may have given you enough buzz words to google what you need.

 

[mutley1]

Thanks Savil

 

[dhookom]

A quick addition to Savil's advice.
I would
- create a simple pass-through query to your SQL database
- create a simple form for users to enter criteria
- have a button that runs some DAO code to build a SQL statement to update the SQL property of the p-t query
- have the code open the query for the user to view

  Dim strSQL as String
  Dim strPTQ as String
  strPTQ = "qsptYourPTQuery"
  strSQL = "SELECT * FROM [YourTableOrView] WHERE 1=1 "
  If Not IsNull(Me.txtAreaCode ) Then
    strSQL = strSQL & " AND [AreaCode] = '" & Me.txtAreaCode & "' "
  End If
  If Not IsNull(Me.txtStartDate) Then
    strSQL = strSQL & " AND DateField >='" & Me.txtStartDate & "' "
  End If
   ' more similar code
  Currentdb.QueryDefs(strPTQ).SQL = strSQL
  DoCmd.OpenQuery strPTQ

Duane
Hook'D on Access
MS Access MVP