Solved

Access connect to SQL Server syntax

Posted on 2007-11-24
13
259 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 92

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 92

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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 

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 500 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

739 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