Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access connect to SQL Server syntax

Posted on 2007-11-24
13
Medium Priority
?
271 Views
Last Modified: 2010-03-20
I am trying to connect to a MS SQL Server 2000 database from an Access application using the code in the attached code snippet.  I get an Error 3131 Error in From clause message when I open the form.

Can you help me with this please.

Thanks,
merdeka
Set rs01 = db.OpenRecordset _
("SELECT DISTINCT tbl_WBS01.code_L1, tbl_WBS01.desc_L1 " & _
"FROM (tbl_EmpResourceType INNER JOIN ((((tbl_WBS01 INNER JOIN tbl_WBS02 ON " & _
"tbl_WBS01.code_L1 = tbl_WBS02.w01code_L1) INNER JOIN tbl_WBS03 ON " & _
"tbl_WBS02.code_L1 = tbl_WBS03.w02code_L1) INNER JOIN tbl_WBS04 ON " & _
"tbl_WBS03.code_L1 = tbl_WBS04.w03code_L1) INNER JOIN tbl_WBS05 ON " & _
"tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) in ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB;" & _
"ON tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) " & _
"INNER JOIN (tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID " & _
"WHERE (((tbl_EmpResourceType.PersonCode)=""" & [Forms]![MyTimesheet]![txtUsername] & """) AND " & _
"((tbl_Phase.Status)<>""C""))", dbOpenDynaset)

Open in new window

0
Comment
Question by:merdeka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
13 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20344241
Try:

Set rs01 = db.OpenRecordset _
("SELECT DISTINCT tbl_WBS01.code_L1, tbl_WBS01.desc_L1 " & _
"FROM (tbl_EmpResourceType INNER JOIN ((((tbl_WBS01 INNER JOIN tbl_WBS02 ON " & _
"tbl_WBS01.code_L1 = tbl_WBS02.w01code_L1) INNER JOIN tbl_WBS03 ON " & _
"tbl_WBS02.code_L1 = tbl_WBS03.w02code_L1) INNER JOIN tbl_WBS04 ON " & _
"tbl_WBS03.code_L1 = tbl_WBS04.w03code_L1) INNER JOIN tbl_WBS05 ON " & _
"tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) in ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB;" & _
"ON tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) " & _
"INNER JOIN (tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID " & _
"WHERE (((tbl_EmpResourceType.PersonCode)='" & [Forms]![MyTimesheet]![txtUsername] & "') AND " & _
"((tbl_Phase.Status)<>'C'))", dbOpenDynaset)
0
 

Author Comment

by:merdeka
ID: 20344464
Hi mathewspatrick,

I got the same error.  What did you change in your version?

Thanks,
merdeka
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20344592
merdeka,

On lines 11 and 12, I converted the "doubled up" double quotes to single quotes.

Regards,

Patrick
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:merdeka
ID: 20344698
Hi mathewspatrick,

I copied your code into the query but it produced the same error.  

I have attached the entire code up to the query in question in the Code Snippet below.  The first two versions have been 'remmed' out.  I was going to use strConnCostCode once the query ran.

Thanks,
merdeka

Private Sub Form_Load()
On Error GoTo Err_Form_Load
 
'Load WBS0 treeview
 
Dim strConnCostCode As String
strConnCostCode = "ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB;"
 
Dim strSQL01a As String
Dim strSQL01b As String
Dim strSQL01c As String
Dim strSQL01d As String
 
Dim strSQL02a As String
Dim strSQL02b As String
Dim strSQL02c As String
 
Dim strSQL03a As String
Dim strSQL03b As String
 
Dim strSQL04a As String
 
 
 Dim W05Node As Node
 
 Dim rs01 As Recordset
 Dim rs02 As Recordset
 Dim rs03 As Recordset
 Dim rs04 As Recordset
 Dim rs05 As Recordset
 
 Dim db As Database
 Set db = CurrentDb
 
 'The following worked in Access:
' Set rs01 = db.OpenRecordset _
'("SELECT DISTINCT tbl_WBS01.code_L1, tbl_WBS01.desc_L1 " & _
'"FROM (tbl_EmpResourceType INNER JOIN ((((tbl_WBS01 INNER JOIN tbl_WBS02 ON " & _
'"tbl_WBS01.code_L1 = tbl_WBS02.w01code_L1) INNER JOIN tbl_WBS03 ON " & _
'"tbl_WBS02.code_L1 = tbl_WBS03.w02code_L1) INNER JOIN tbl_WBS04 ON " & _
'"tbl_WBS03.code_L1 = tbl_WBS04.w03code_L1) INNER JOIN tbl_WBS05 ON " & _
'"tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) ON tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) " & _
'"INNER JOIN (tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
'"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID " & _
'"WHERE (((tbl_EmpResourceType.PersonCode)=""" & [Forms]![MyTimesheet]![txtUsername] & """) AND " & _
'"((tbl_Phase.Status)<>""C""))", dbOpenDynaset)
 
'Didn't work so I asked EE:
' Set rs01 = db.OpenRecordset _
'("SELECT DISTINCT tbl_WBS01.code_L1, tbl_WBS01.desc_L1 " & _
'"FROM (tbl_EmpResourceType INNER JOIN ((((tbl_WBS01 INNER JOIN tbl_WBS02 ON " & _
'"tbl_WBS01.code_L1 = tbl_WBS02.w01code_L1) INNER JOIN tbl_WBS03 ON " & _
'"tbl_WBS02.code_L1 = tbl_WBS03.w02code_L1) INNER JOIN tbl_WBS04 ON " & _
'"tbl_WBS03.code_L1 = tbl_WBS04.w03code_L1) INNER JOIN tbl_WBS05 ON " & _
'"tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) in ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB;" & _
'"ON tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) " & _
'"INNER JOIN (tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
'"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID " & _
'"WHERE (((tbl_EmpResourceType.PersonCode)=""" & [Forms]![MyTimesheet]![txtUsername] & """) AND " & _
'"((tbl_Phase.Status)<>""C""))", dbOpenDynaset)
  
 'new from EE (looks the same as above to me)
Set rs01 = db.OpenRecordset _
("SELECT DISTINCT tbl_WBS01.code_L1, tbl_WBS01.desc_L1 " & _
"FROM (tbl_EmpResourceType INNER JOIN ((((tbl_WBS01 INNER JOIN tbl_WBS02 ON " & _
"tbl_WBS01.code_L1 = tbl_WBS02.w01code_L1) INNER JOIN tbl_WBS03 ON " & _
"tbl_WBS02.code_L1 = tbl_WBS03.w02code_L1) INNER JOIN tbl_WBS04 ON " & _
"tbl_WBS03.code_L1 = tbl_WBS04.w03code_L1) INNER JOIN tbl_WBS05 ON " & _
"tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) in ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB;" & _
"ON tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) " & _
"INNER JOIN (tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID " & _
"WHERE (((tbl_EmpResourceType.PersonCode)='" & [Forms]![MyTimesheet]![txtUsername] & "') AND " & _
"((tbl_Phase.Status)<>'C'))", dbOpenDynaset)
  

Open in new window

0
 
LVL 31

Accepted Solution

by:
James Murrell earned 1500 total points
ID: 20346298
Put brackets "[]" around the table names.
0
 

Author Comment

by:merdeka
ID: 20435322
Hi cs97iim3,

Where did you get that name?

Sorry for the late reply, I have a day job too.  Below is a simpler attempt to connect using the square brackets but I get a runtime  error 97 Object Variable or With block variable not set at:

qdfRecords.Connect = "ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB"

possibly because the line above it (see below) is wrong.

I often wonder if ssomething that seems like it should be easy has to be this difficult.

Thanks for your help,
merdeka


Private Sub Form_Load()
 
Dim DB As Database
Dim dbsCurrent As Database
Dim rs As Recordset
Dim qdfRecords As QueryDef
Dim strSQL As String
 
Set dbsCurrent = CurrentDb
qdfRecords.Connect = "ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB"
 
strSQL = "SELECT tbl_ProcurementCode.Code_L1, tbl_ProcurementCode.Desc_L1, "
strSQL = strSQL & "tbl_ProcurementCode.LastUser, tbl_ProcurementCode.projperiod "
strSQL = strSQL & "FROM tbl_ProcurementCode IN '' [ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB;];"
 
qdfRecords.SQL = strSQL
 
Set rs = qdfRecords.OpenRecordset()
   
Set rs = Nothing
 
 
End Sub

Open in new window

0
 

Author Comment

by:merdeka
ID: 20489751
Hi cs97iim3,

I tried adding the square brackets some time ago and now the query runs producing the error:

Error 3146 ODBC -- Call Failed.

I have attached the latest code in the snippet window.

Thanks,
merdeka
Private Sub Form_Load()
On Error GoTo Err_Form_Load
 
'Load WBS0 treeview
 
Dim strConnCostCode As String
strConnCostCode = "ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB;"
 
Dim strSQL01a As String
Dim strSQL01b As String
Dim strSQL01c As String
Dim strSQL01d As String
 
Dim strSQL02a As String
Dim strSQL02b As String
Dim strSQL02c As String
 
Dim strSQL03a As String
Dim strSQL03b As String
 
Dim strSQL04a As String
 
 
 Dim W05Node As Node
 
 Dim rs01 As Recordset
 Dim rs02 As Recordset
 Dim rs03 As Recordset
 Dim rs04 As Recordset
 Dim rs05 As Recordset
 
 Dim db As Database
 Set db = CurrentDb
 
 'The following worked in Access:
' Set rs01 = db.OpenRecordset _
'("SELECT DISTINCT tbl_WBS01.code_L1, tbl_WBS01.desc_L1 " & _
'"FROM (tbl_EmpResourceType INNER JOIN ((((tbl_WBS01 INNER JOIN tbl_WBS02 ON " & _
'"tbl_WBS01.code_L1 = tbl_WBS02.w01code_L1) INNER JOIN tbl_WBS03 ON " & _
'"tbl_WBS02.code_L1 = tbl_WBS03.w02code_L1) INNER JOIN tbl_WBS04 ON " & _
'"tbl_WBS03.code_L1 = tbl_WBS04.w03code_L1) INNER JOIN tbl_WBS05 ON " & _
'"tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) ON tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) " & _
'"INNER JOIN (tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
'"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID " & _
'"WHERE (((tbl_EmpResourceType.PersonCode)=""" & [Forms]![MyTimesheet]![txtUsername] & """) AND " & _
'"((tbl_Phase.Status)<>""C""))", dbOpenDynaset)
 
'Didn't work so I asked EE:
' Set rs01 = db.OpenRecordset _
'("SELECT DISTINCT tbl_WBS01.code_L1, tbl_WBS01.desc_L1 " & _
'"FROM (tbl_EmpResourceType INNER JOIN ((((tbl_WBS01 INNER JOIN tbl_WBS02 ON " & _
'"tbl_WBS01.code_L1 = tbl_WBS02.w01code_L1) INNER JOIN tbl_WBS03 ON " & _
'"tbl_WBS02.code_L1 = tbl_WBS03.w02code_L1) INNER JOIN tbl_WBS04 ON " & _
'"tbl_WBS03.code_L1 = tbl_WBS04.w03code_L1) INNER JOIN tbl_WBS05 ON " & _
'"tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) in ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB;" & _
'"ON tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) " & _
'"INNER JOIN (tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
'"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID " & _
'"WHERE (((tbl_EmpResourceType.PersonCode)=""" & [Forms]![MyTimesheet]![txtUsername] & """) AND " & _
'"((tbl_Phase.Status)<>""C""))", dbOpenDynaset)
  
 'new from EE (looks the same as above to me)
Set rs01 = db.OpenRecordset _
("SELECT DISTINCT tbl_WBS01.code_L1, tbl_WBS01.desc_L1 " & _
"FROM (tbl_EmpResourceType INNER JOIN ((((tbl_WBS01 INNER JOIN tbl_WBS02 ON " & _
"tbl_WBS01.code_L1 = tbl_WBS02.w01code_L1) INNER JOIN tbl_WBS03 ON " & _
"tbl_WBS02.code_L1 = tbl_WBS03.w02code_L1) INNER JOIN tbl_WBS04 ON " & _
"tbl_WBS03.code_L1 = tbl_WBS04.w03code_L1) INNER JOIN tbl_WBS05 ON " & _
"tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) " & _
"ON tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) " & _
"INNER JOIN (tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID in '' [ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB;] " & _
"WHERE (((tbl_EmpResourceType.PersonCode)='" & [Forms]![MyTimesheet]![txtUsername] & "') AND " & _
"((tbl_Phase.Status)<>'C'))", dbOpenDynaset)
  
  
 With tvWBS0.Nodes
  
  While rs01.EOF = False
      .Add , , "a" & rs01!code_L1, rs01!code_L1 & " - " & rs01!desc_L1
   Set rs02 = db.OpenRecordset _
("SELECT DISTINCT tbl_WBS02.w01code_L1, tbl_WBS02.code_L1, tbl_WBS02.desc_L1 " & _
"FROM (tbl_EmpResourceType INNER JOIN (((tbl_WBS02 INNER JOIN tbl_WBS03 ON " & _
"tbl_WBS02.code_L1 = tbl_WBS03.w02code_L1) INNER JOIN tbl_WBS04 ON tbl_WBS03.code_L1 = tbl_WBS04.w03code_L1) " & _
"INNER JOIN tbl_WBS05 ON tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) ON " & _
"tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) INNER JOIN " & _
"(tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID in '' [ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB;] " & _
"WHERE (((tbl_EmpResourceType.PersonCode)=""" & [Forms]![MyTimesheet]![txtUsername] & """) AND " & _
"((tbl_Phase.Status)<>""C""))", dbOpenDynaset)
 
 
'      ("select * from tbl_WBS02 where w01code_L1=""" & _
'       rs01!Code_L1 & """ order by Desc_L1", dbOpenDynaset)
     
     While rs02.EOF = False
      .Add "a" & rs01!code_L1, tvwChild, _
           "b" & rs02!code_L1, rs02!code_L1 & " - " & rs02!desc_L1
      Set rs03 = db.OpenRecordset _
("SELECT DISTINCT tbl_WBS03.w01code_L1, tbl_WBS03.code_L1, tbl_WBS03.desc_L1 " & _
"FROM (tbl_EmpResourceType INNER JOIN ((tbl_WBS03 INNER JOIN tbl_WBS04 ON " & _
"tbl_WBS03.code_L1 = tbl_WBS04.w03code_L1) INNER JOIN tbl_WBS05 ON " & _
"tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) ON tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) " & _
"INNER JOIN (tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID in '' [ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB;] " & _
"WHERE (((tbl_EmpResourceType.PersonCode)=""" & [Forms]![MyTimesheet]![txtUsername] & """) AND " & _
"((tbl_Phase.Status)<>""C""))", dbOpenDynaset)
 
        
'        ("select * from tbl_WBS03 where w02code_L1=""" & _
'         rs02!Code_L1 & """ order by Desc_L1", dbOpenDynaset)
        
        While rs03.EOF = False
      .Add "b" & rs02!code_L1, tvwChild, _
           "c" & rs03!code_L1, rs03!code_L1 & " - " & rs03!desc_L1
      Set rs04 = db.OpenRecordset _
("SELECT DISTINCT tbl_WBS04.w01code_L1, tbl_WBS04.code_L1, tbl_WBS04.desc_L1 " & _
"FROM (tbl_EmpResourceType INNER JOIN (tbl_WBS04 INNER JOIN tbl_WBS05 ON " & _
"tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) ON tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) " & _
"INNER JOIN (tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID in '' [ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB;] " & _
"WHERE (((tbl_EmpResourceType.PersonCode)=""" & [Forms]![MyTimesheet]![txtUsername] & """) AND " & _
"((tbl_Phase.Status)<>""C""))", dbOpenDynaset)
        
'        ("select * from tbl_WBS04 where w03code_L1=""" & _
'         rs03!Code_L1 & """ order by Desc_L1", dbOpenDynaset)
        
        While rs04.EOF = False
      .Add "c" & rs03!code_L1, tvwChild, _
           "d" & rs04!code_L1, rs04!code_L1 & " - " & rs04!desc_L1
      Set rs05 = db.OpenRecordset _
("SELECT DISTINCT tbl_WBS05.w01code_L1, tbl_WBS05.code_L1, tbl_WBS05.desc_L1 " & _
"FROM (tbl_EmpResourceType INNER JOIN tbl_WBS05 ON " & _
"tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) INNER JOIN (tbl_Phase " & _
"INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID in '' [ODBC;DSN=Core;UID=john;PWD=;DATABASE=CostCodeDB;] " & _
"WHERE (((tbl_EmpResourceType.PersonCode)=""" & [Forms]![MyTimesheet]![txtUsername] & """) AND " & _
"((tbl_Phase.Status)<>""C""))", dbOpenDynaset)
 
        
'        ("select * from tbl_WBS05 where w04code_L1=""" & _
'         rs04!Code_L1 & """ order by Desc_L1", dbOpenDynaset)
   
      While rs05.EOF = False
 '       .Add "d" & rs04!Code_L1, tvwChild, , rs05!Desc_L1
        .Add "d" & rs04!code_L1, tvwChild, _
          "e" & rs05!code_L1, Mid(rs05!code_L1, 29, 10) & " * " & rs05!desc_L1
        rs05.MoveNext
          
               Wend
              rs04.MoveNext
              rs05.Close
          Wend
          rs03.MoveNext
          rs04.Close
      Wend
      rs02.MoveNext
      rs03.Close
    Wend
    rs01.MoveNext
    rs02.Close
  Wend
  rs01.Close
 End With
 
 Me!tvWBS1.Nodes.Clear
 Me!tvWBS2.Nodes.Clear
 
 
 'Cleanup
 Set db = Nothing
 Set rs01 = Nothing
 Set rs02 = Nothing
 Set rs03 = Nothing
 
Exit_Form_Load:
    Exit Sub
 
Err_Form_Load:
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description, vbExclamation, "Error"
    Resume Exit_Form_Load
 
End Sub
 
Private Sub LstSelectedWork_Click()
DoCmd.OpenForm ("frmDisplayWorkDescSelectWork")
End Sub
 
Private Sub tvWBS0_NodeClick(ByVal Node As Object)
'copy selected WBS0 node value to textbox for use in WBS1 treeview
If Left(Node.Key, 1) = "e" Then
'Me!txtWBS0Node = Node.Text
Me!txtWBS0Node = Mid(Node.Key, 2, 38)
Me!txtProjName = Node.Text
End If
End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:James Murrell
ID: 20490514
sorry old uni login:

try this http://support.microsoft.com/kb/160762
0
 

Author Comment

by:merdeka
ID: 20595569
Hi cs97jjm3,

I read the Microsoft article regarding the Error 3146 but it only applies to SQL Server 6.5 or earlier whereas I am using SQL Server 2000.  My Access 2003 program is running on Windows 2000.

Anyway, this error has stopped happening even though I haven't changed any code.

When I 'mouse-over' on of the record variables in the code I get a message Object variable or With block not set which I presume is because there is no connection to the database.

Thanks,
merdeka.
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 20597586
whoops sorry. gald all is ok now
0
 

Author Comment

by:merdeka
ID: 20598919
Hi cs97jjm3,

Actually all isn't OK it's just that error message that no longer appears.  I still can't connect to the SQL Server 2000 database.

Thanks,
merdeka
0
 

Author Comment

by:merdeka
ID: 21077063
The square bracket solution was correct however there was another problem which I didn't know about that was also causing the problem (now resolved).  I therefore accept the square bracket solution and appologize for not responding sooner.

merdeka
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question