Access getting data via VBA from SQL Server 2008
I am creatiing a data entry form in Access that wites data to an Access table. I need to be able to get one field from a master file on SQL Server call Mast_ID. In the data entry form I need to verify that the Mast_ID Just entered in data entry has a correspond Mast_ID on SQL Server. I have two linked tables to SQL Server in my Access .mbd that run queries very well using ODBC and the QBE grid. I am using the following VBA code code
'Set up select query
Dim cnnluid As ADODB.Connection
Set cnnluid = CurrentProject.Connection
Dim MaMIDRecordSet As New ADODB.Recordset
MaMIDRecordSet.ActiveConnection = cnnluid
'Add SQL to string for looking up Master_ID in
'Master Accounts Table on Server
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)='Card_No'))"
'Run Select query with String containing SQL
The system tells me that "ODBC Call failed"
Hope you can be of help.
Thanks in advance