help with SQL Statements

[fjclaus]

I am trying to write code for Running SQL statements on linked tables in Access. It does not seem to be working for me.

Here is the SQL statement I have so far. Can anyone please help me figure out what went wrong?

Searchsql = "Select [" & SearchTble & "].* from [" & SearchTble & "] Where ((([" & SearchTble & "].[ORDER #]) = '" & SearchData & "'));"


It is supposed to run the statement on a linked table. I was told that what it is doing is running it on a table within access not a linked one. Any thoughts on how to fix it?

Frederick

 

[shacket]

A linked table in Access essentially functions as an actual table. The SQL is running against the table (linked or not) listed as your SearchTble variable. Whether or not it is linked should not be an issue.

(This assumes that 'SearchTble' is a variable - that's what it looks like it is)

[This message has been edited by shacket (edited 10-03-2001).]

 

[fjclaus]

Thanks for the help. Here my whole code for the "onclick" it does not seem to work write. Any ideas why?

Private Sub Search_Click()
Dim db As Object ' The .mdb file that was selected in optgrp1
Dim SearchTble As String ' The table within the Database to be searched
Dim SearchData As String ' The data in the "text4" box to be searched for.
Dim Searchsql As String
Dim rstSearchRslt As Recordset ' The recordset where the results are to be placed.
Dim cnt As Integer


Set db = OpenDatabase(Me.lstDatabaseName.Value, True, True)
SearchTble = Me.Ship_Date.Value
Me.Text4.SetFocus
SearchData = Me.Text4.Text
Searchsql = "Select [" & SearchTble & "].* from [" & SearchTble & "] Where ((([" & SearchTble & "].[ORDER #]) = '" & SearchData & "'));"

Set rstSearchRslt = db.OpenRecordset(Searchsql)
With rstSearchRslt
.MoveLast
.MoveFirst
cnt = .RecordCount
If .RecordCount > 0 Then
DoCmd.RunSQL "INSERT INTO trk_searchresults SELECT [" & SearchTble & "].* FROM trk_searchresults AS [" & SearchTble & "] WHERE ((([" & SearchTble & "].[ORDER #])='" & SearchData & "'));"
End If
End With

 

[shacket]

Let me try to troubleshoot this. A few questions:

Your table reference (SearchTble) is from a field called Ship_Date. Is that really the name of a table?

If I read your code correct, you are trying to establish whether any records meet the criterion and, if so, add them to your table trk_searchresults. You can get around this without using recordsets. Use the DCount function (which counts rows in a recordset that meet a certain criteria). The "shell" of the code might look like this:

If DCount ("*",Me.Ship_Date,"[Order #] = '" & Me.Text4 & "'") > 0 Then DoCmd.RunSQL (YourSQLString)

Looking through your code, there is a lot of redundant things you are asking Access to do, i.e.:

-Setting the focus to Me.Text4
-MoveFirst and MoveLast with your recordset
-Assigning .RecordCount to variable cnt when you don't use the variable

You also have an If statement within a With statement. I don't know if that works or not, but I have always found trouble doing things like that. I try to keep my with statements very concise. If you need to reference the recordset, you can do so without a with statement by using:

rs.RecordCount

and the like.

You mentioned that this code doesn't "seem" to be working. What is it doing? Are you getting an error message? Is it changing any of your data?

 

[fjclaus]

What it is doing is this. It seems to be searching the databases and tables I asked it to, but when it is done it is supposed to open the trk_searchresults table and show me it's findings. This table does not open, and when I manually open the table there is nothing in it. I know for a fact that the information I am asking it to find is there though.

Would it be possible for you to edit my code with what you mentioned? I am new to this, and don't know what to take out in order to replace it with the Dcount statement you gave me.

Thanks,
Frederick

 

[fjclaus]

this is what I did. I didn't remove any of the old code, I just commented it out so it is still there, but the program does not read it. Did I do it right? Why is it still not working?


Private Sub Search_Click()
Dim db As Object ' The .mdb file that was selected in optgrp1
Dim SearchTble As String ' The table within the Database to be searched
Dim SearchData As String ' The data in the "text4" box to be searched for.
Dim Searchsql As String
Dim rstSearchRslt As Recordset ' The recordset where the results are to be placed.
Dim cnt As Integer


Set db = OpenDatabase(Me.lstDatabaseName.Value, True, True)
SearchTble = Me.Ship_Date.Value
Me.Text4.SetFocus
SearchData = Me.Text4.Text
Searchsql = "SELECT [" & SearchTble & "].* FROM [" & SearchTble & "] WHERE ((([" & SearchTble & "].[ORDER #])= '" & SearchData & "'));"

Set rstSearchRslt = db.OpenRecordset(Searchsql)
'With rstSearchRslt
'.MoveLast
'.MoveFirst
'cnt = .RecordCount
'If .RecordCount > 0 Then
'DoCmd.RunSQL "INSERT INTO trk_searchresults SELECT [" & SearchTble & "].* FROM trk_searchresults AS [" & SearchTble & "] WHERE ((([" & SearchTble & "].[ORDER #])= '" & SearchData & "'));"
'End If
'End With

If DCount("*", Me.Ship_Date, "[Order #] = '" & Me.Text4 & "'") > 0 Then DoCmd.RunSQL ("INSERT INTO trk_searchresults SELECT [" & SearchTble & "].* FROM trk_searchresults AS [" & SearchTble & "] WHERE ((([" & SearchTble & "].[ORDER #])= '" & SearchData & "'));")

'rstSearchRslt.Filter (database.SearchTble.SearchData)


'Select Case optgrp2
'Case 1
' s_PathComplete = "L:\Cust*.mdb"
' s_PathRoot = "L:\"
'Case 2
' s_PathComplete = "D:\Cust*.mdb"
' s_PathRoot = "D:\"
'End Select

End Sub

 

[Pat Hartman]

If the tables are linked, why are you opening a database object that points to a database other than the current one?

Take a look at the following if you want to see how to reference two database objects in the same piece of code. Keep in mind that the table you are appending the data to is in the current database while the data you are trying to retrieve is in a foreign database. http://www.access-programmers.co.uk/ubb/Forum2/HTML/001073.html

Also, look up "Source Property(Microsoft Access)" in help. If the tables are really not linked as you said, this is probably what you need to access them properly.