Open an SQL statement in Project?

[BSman]

Is there a way to open an SQL statement in Access Project (2003) in VBA code? Basically, I'm looking to temporarily create an SQL statement and then open it, normally to get the typical spreadsheet-like display of the result. This would be defining a pass through SQL query in regular Access that is linked to a SQL Server database and then opening that pass through query, and then deleting the pass through query.

Bob

 

[sgravitt1]

I don't pretend to be the expert (believe me) however here is some generic code for querying a database. You can place this in a module, in a form event procedure or even a seperate *.vbs file. The syntax of the query is pretty sensitive.

I'm not sure as to what it is exactly you are trying to accomplish by deleting the query but as you can see this code opens and closes and if needed you can change the constant "adLockOptimistic" to another connection type such as read only, with a value of 1 instead of 3.

- anyway this could get you started on your idea's -

if you feel like elaborating on the details of the situation or of the results you want, there is a good chance myself or others can pitch in on other specific solutions

'*************

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = eventlogs.mdb"

objRecordSet.Open "SELECT * FROM EventTable " & _
"WHERE EventCode = 1054", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

msgbox "Number of records: " & objRecordset.RecordCount

'you can also ad a for each... next statement here:
For Each objRecord in objRecordSet
'do stuff
objRecordset.movenext
Next

objRecordSet.Close
objConnection.Close
'***************


HTH,
Sg284

 

[BSman]

Unfortunately, the code you listed is for use with an Access back end, not for use with Access Project, which uses a SQL Server back end.

I found something in a book about Access Project that talks about creating a form that uses a recordset as its source. I've tried a variety of methods of creating a connection, but nothing seems to finally work. The closest I seem to have gotten is where when I tried to open the connection I got the error message login failed (to the SQL Server databaase). However, I went in to SQL Server and verified that the userid and password that I was using in the connection string were correct, so it's really puzzling. Of course, the error messages in Access are sometimes very misleading, so it might be something else that's not related (and not obvious). Since I'm trying to create a query "on the fly" (ad hoc) within the application and display the results to the user in a spreadsheet (datasheet) type of format, the form seems like the only way to do that....once you can get the creation of a recordset to work correctly. The basic trick with the form is to create an unbound form with on load VBA code that will create the recordset and then sets the recordset source property of the form to use the recordset that's been created. Then open the form, go to the design mode and set up a text box for each column you want to include on the datasheet, and set the display mode of the form to datasheet.

It's more hassle than the simple process in regular Access (even when using a SQL Server back end with pass through queries), where you can just create a pass through query and open it and it is automatically displayed as a datasheet/spreadsheet format.

I'm thinking that part of my problem with the recordset may be that I need to somehow set the ADO recordset as a type that will display the data buy not allow updates.

Bob

 

[sgravitt1]

sorry to not be more helpful - I havent worked a lot with access projects, but I am sure what you are trying to do is possible.

Let me see if I understand you situation, you're access project is linked to an sql database and working fine, and you want to have a form in datasheet view that only displays the information, correct?

I honestly dont think you need to do this with coding, although it should be possible with the correct syntax and information.

I believe you should be able to just make the form with the datasource of the table, with all the textbox objects bound, but n the form properties under the data tab, you should be able to put right in there whether you want the field locked, to allow additions/edit/deletion etc which would give the effect that the information is untouchable - you can also edit the way it connects to the table -> via dynaset or snapshot, see if that gets you the results you need

have you tried the following code specifically for SQL?

Const adOpenStatic = 3
Const adLockOptimistic = 3 ' remember this can be read only (value = 1 instead of 3)

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider=SQLOLEDB;Data Source=*computername*atl-sql-01;" & _
"Trusted_Connection=Yes;Initial Catalog=Northwind;" & _
"User ID=fabrikam\kenmyer;Password=34DE6t4G!;"

objRecordSet.Open "SELECT * FROM Customers", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

 

[BSman]

I'm trying to figure out if I can modify a generic form I created a long time ago that provides an easy method for users to perform ad hoc queries on tables/queries. You (the programmer) has to set up the appropriate information in a table (the name of the table or query, each column that can be used for criteria and/or sorting and/or grouping or totals for a grouping query, type of data for each column and a combo box if appropriate, etc.). That table is used to populate the drop downs on the ad hoc query builder form. Once the user selects the criteria and how to sort the results, the form builds two queries (originally for an Access back end, now modified to create a pass through query for a SQL Server back end). One returns all of the matching records, the order group by the sort column(s) and totals or counts the appropriate columns. The results are displayed by opening each query, and because they open in a spreadsheet format, the results can easily be copied by the user into Excel.

I'm hoping that I can modify the code created by this ad hoc query form to accomplish the same results with an Access Project. However, Access Project is not linked (in the normal Access sense) to the SQL database. There are no Jet queries or tables. Basically, I'm already creating SQL code that can be executed within SQL Server. The problems is how to get it to run and to display the results in the spreadsheet format.

Anyway, after I dimensioned objRecordSet, your code worked. Thanks. However, I can't set the recordset of the form as equal to objRecordSet. In fact, I get an error message when I try to display the value of a column in the recordset. Perhaps it needs to be a different type of recordset.

At least we're getting closer.

Bob