ADO with SQL Problem

i have got this code from here

http://www.vbforums.com/showthread.php?t=416225

i get this error for some reason:

Data type mismatch in criteria expression

this is the code:

What type of field is Serial?

text although it has a number in it

Then you need single quotes around the entry.

if single qoutes are '' then now the value = 0 and i still get the error

the full code i am using is :

Code:

---

Private Sub cmdSearch_Click()
Dim strSQL As String
'build the SQL statement based on what the user typed in txtSearch
strSQL = "SELECT * FROM tblActivation"
If txtSearch.Text <> "" Then
strSQL = strSQL & " WHERE Serial = " & Val(txtSearch.Text)
End If
'close the recordset (required before reloading it)
rs.Close
'load the new data
rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
'show the data
fillfields
End Sub

---

by the way i put '31031101059811167' in the textbox

If the Serial field in the DB is text is doesnt mater if it is only holding numbers it is still text and the values must be surrounded by single qoutes like this:

Code:

---

Dim strSQL As String
'build the SQL statement based on what the user typed in txtSearch
strSQL = "SELECT * FROM tblActivation"
If txtSearch.Text <> "" Then
strSQL = strSQL & " WHERE Serial = '" & Trim(Replace(txtSearch.Text,"'","'')) & "'"
End If

---

i get a syntax error on this line:

Code:

---

strSQL = strSQL & " WHERE Serial = '" & Trim(Replace(txtSearch.Text,"'","'')) & "'"

---

I'm missing a double qoute after the two single qoutes before the closing paren for the Replace function.

thanks that works now one more thing.

When i do a search for something that isnt in the database i get a msgbox

Either you are at the first record or at the last.

is there a way that i can get rid of this message and make it set a variable as false.

Try this:

Code:

---

if rs.State = adStateOpen Then rs.Close
'load the new data
rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
If Not rs.BOF and Not rs.EOF Then
 'show the data
 fillfields
Else
 MsgBox "No Records Found that match the search crieteria."vbOKOnly + vbInformation,"No Data Found"
End If

---

i have moved the db to the web now i get invalid file nam

"Data Source= http://ripway.com/act/activate.mdb" 'this is the connection string explained in the notes section.

ok im working with chris on this and i figured it out you use the ftp e.g.
"Data Source= ftp.ripway.com/act/activate.mdb"
but it also includes my desktop as part of the address how do i sort that out?
the error looks like this...

Thanks in advance
Danny

VB might not like the # in the db path name.

I also received that error when my select statement was incorrect trying connect to a FoxPro db.

I didn't tell it to use any part of my computer as you can see in the image (its the bit just above the error box) nor did i tell it to add a #, can anyone figure how to make it ignore my computer and only use the server please?

thanks in advance

Access does not support being used on the web, tho there is a (unreliable) way to do that, as shown in our Database FAQs.

Note that this cannot use FTP, as that is File Transfer Protocol. Oh, and the reason for the desktop path being added there is that you did not specify a protocol (FTP), so the default (file) was assumed, and as it was not a full file path, it was appended to the 'current' directory.

thanks ill look in the database FAQS

i got it connecting now using a username and password but it cant open the file due to 'The workgroup information file is missing or opened exclusively by another user'
btw chris has uploaded the workgroup information file

Dannyg is working with me on this, we have now got it to connect to the db but by the looks of it it cant open the record set.

This code is highlighted:

Code:

---

rs.Open "tblActivation", cn, adOpenKeyset, adLockPessimistic, adCmdTable

---

This is the Error:

Can anyone help?

I'm afraid I have never used Access on the web (I have always used 'real' database systems like SQL Server instead), and have never seen that error before either.. I can only assume that it means that the specified table cannot be found in the database.

Try using

Code:

---

rs.open "tablename" ,cn,adOpenDynamic,adLockOptimistic

---

It looks more like the error is telling you that IIS can't connect properly to Access.