Access 2003 using Linked tables from SQL Server2008

[jpl458]

I am doing a data entry form in access 2003 that enters data into and ACCESS table. But I have a linked table in SQL Server 2008, and queries created ACCESS using the QBE grid run fine. I can joint the linked tables with ACCESS tables and update the linked SQL Server table. But, ihave a problem doing a select query to the linked table from VBA, and I am stumped. After an Account number is entered, on lost focus I run the following code;

'Add SQL to string for looking up Master_ID in
'Master Accounts Table on Server
Dim StrLUMID As String
StrLUMID = "SELECT Master_ID FROM"
StrLUMID = StrLUMID + " dbo_Master_Accounts"
StrLUMID = StrLUMID + " WHERE (((dbo_Master_Accounts.Master_ID)=Card_No))"

'Set up select query
Dim cnndbo As ADODB.Connection
Set cnndbo = CurrentProject.Connection
Dim rsdbo As New ADODB.Recordset
rsdbo.ActiveConnection = cnndbo
rsdbo.CursorType = adOpenDynamic
rsdbo.LockType = adLockOptimistic

'Run Select query with String containing SQL

rsdbpen StrLUMID

'Interrogate the returned recordset to see if it
'matches the input card number. If not equal
'Display Message. If match, keep on truclin

If rsdbo.Fields(0).Value <> Card_No Then
MsgBox "Invalid Card Number"
Card_No.SetFocus
Else
End If

It bombs on the rsdbpen with "No value given for one or more of the required peramiters" and i can't get past this.

It's been trying, so any help would be appreciated.

Thanks in advance

 

[dhookom]

What is Card_No? Is it a memory variable or a field in dbo_Master_Accounts as your code assumes.

Duane
Hook'D on Access
MS Access MVP

 

[jpl458]

Card_No is a text box on the data entry form. I want to see if the contents of Card_No exists in the Master_Accounts table in SQL Server.

Hope this helps.

Thanks for responding

 

[dhookom]

Take a look at the following and picture how the query will be able to resolve Card_no. It's going to look for a field in the table and if it doesn't find it, your sql will fail.

    StrLUMID = "SELECT Master_ID FROM"
    StrLUMID = StrLUMID + " dbo_Master_Accounts"
    StrLUMID = StrLUMID + " WHERE (((dbo_Master_Accounts.Master_ID)=Card_No))"

You might need something like:

[green]    'assuming Master_ID is numeric[/green]
    StrLUMID = "SELECT Master_ID FROM"
    StrLUMID = StrLUMID + " dbo_Master_Accounts"
    StrLUMID = StrLUMID + " WHERE Master_ID=" & Me.Card_No

Duane
Hook'D on Access
MS Access MVP

 

[jpl458]

Card_No is a text field that contains numbers. The reason that it is text is there will be a time when the account numbers will alphameric and based on something other than base 10, say base 26, to accomodate a larger number of account numbers.

I tried your suggestion but got the same result I understand now that I have to indicate where Card_No is coming from in the sql statement.

While I have a bit more knowledge I am still stumped.

Thanks for your help, really appreciate it.

jpl

 

[dhookom]

If card_no is text, try:

    'assuming Master_ID is text
    StrLUMID = "SELECT Master_ID FROM"
    StrLUMID = StrLUMID + " dbo_Master_Accounts"
    StrLUMID = StrLUMID + " WHERE Master_ID='" & Me.Card_No & "'"

Duane
Hook'D on Access
MS Access MVP

 

[jpl458]

I understand the change to the sql, but unfortunately it stil bombs on the open.

Run-time error (80040e10y)
No value given for required peramiters

I don't know what it's refering to, the sql or the open peramiters. Im no sql wizard but what you corrected makes it look ok to me.

Thaks for responding is such a timely fashion.

jpl

 

[dhookom]

You need to learn how to troubleshoot. Try add a line to print the SQL to the debug window

    'assuming Master_ID is text
    StrLUMID = "SELECT Master_ID FROM"
    StrLUMID = StrLUMID + " dbo_Master_Accounts"
    StrLUMID = StrLUMID + " WHERE Master_ID='" & Me.Card_No & "'"
    Debug.Print strLUMID

Then open the debug window (press Ctrl+G) and copy the string into the SQL view of a blank query. What do you get? Is it what you expect?


Duane
Hook'D on Access
MS Access MVP

 

[jedraw]

jpl458,

How does your SQL relate to your form?
As dhookom says, your sql has to refer to Me.Card_No.
Can you tell us more about your form and SQL?

 

[jpl458]

One thing is obvious, I am unclear on a certain concept.

Here is where it stands, and there is progress.

Dim StrLUMID As String
StrLUMID = "SELECT dbo_Master_Accounts.Master_ID FROM"
StrLUMID = StrLUMID + " dbo_Master_Accounts"
StrLUMID = StrLUMID + " where dbo_Master_Accounts.Master_Id="
StrLUMID = StrLUMID + " 'me.Card_No'"

In the above code i can get it to work if I plug some value in to the SQL - that is, replace 'me.Card_No' with say'181667'. then it runs like bandit. Obviously I don't know how to have the sql look at the text value in me.Card_No.
The above code gets past the open but bombs with "BOF or EOF is yes, or the record has been deleted" when I try to manipulate the record set. But as I said it runs great with a literal values in the SQL.

In response to jedraw I am simply taking the contents of the text box (text)on a data entry form and seeing if that number is in a master file in SQL Server.

I think I am close, but only a stogie, not a whole cigar.

Thanks for you help, again

BTW I was surprised at how fast it runs, when it works.

jpl

 

[jpl458]

I think I've found the problem. The following works of there is a corresponding record in the SQL Server master file

Dim StrLUMID As String
StrLUMID = "SELECT dbo_Master_Accounts.Master_ID FROM"
StrLUMID = StrLUMID + " dbo_Master_Accounts"
StrLUMID = StrLUMID + " where dbo_Master_Accounts.Master_Id='" & Me.Card_No & "'"

I found some examples on the web and this is pretty much what dhhokom suggested earlier.

I will play with the record set and the open and see what's going on there. But thanks for your patience with a newbie.

jpl