Syntax of an Open Statement when dealing with ODBC Table Names....

Hi kasowitz,
 [Table Name]
Dave :-)

Thanks for your response Dave....Doesnt seem to be working in my scenario though..here is the code I am trying :

strCnn = "DSN=HEALTHCENT;UID=Admin;PWD=Password;"
    cnn.Open strCnn
    Set rstPatient = New ADODB.Recordset
    rstPatient.CursorType = adOpenDynamic
    rstPatient.LockType = adLockOptimistic
    rstPatient.Open "Billing Header", strCnn, , , adCmdTable


I have tried it using  [Billing Header] , and I get a runtime error 2465 Access cant find the field "|" referred to in your expression.

Any ideas ?
Thanks

Put the table name in Quotes and in brackets.

Private Sub test()
    Dim conn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    Dim strTemp As String
   
    Set conn = CurrentProject.Connection

    rst.Open "[table one]", conn, adOpenDynamic, adLockOptimistic
   
    strTemp = rst!test
   
    rst.Close
    Set rst = Nothing
    conn.Close
    Set conn = Nothing
   
End Sub

I have tried putting the table name in quotes and brackets like you have showed, I get the following error:

run-time error '-2147217900 (80040e14)':

Sorry, This is the full error that I get when putting the table name in quotes and brackets...

run-time error '-2147217900 (80040e14)':
[Pervasice][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Syntax Error:select*from <<???>>[Billing Detail]

Are your references correct?

If you copy the code that I submitted with your table name and run the sub does it still give you an error?

The pervasive sql database we are connecting to contains like 50 tables.  Some named with single names, some with multiple.  When we use our code with a table "Patient" it works.  When we use our code with table "billing detail" it doesnt.  So I think our references are correct.  Your code doesnt work, because we are making the ADO odbc connection where you are looking for a table in Access I think...but then again I am the one with the question.  Thanks for your help...any other ideas?

This is using a table in Sql server linked to an Access front end using a DSN.

test table is the name of the table on Sql serer.

Notice that I changedthe table name to a sql statemnet and then passed that to the recordset.

Private Sub test()
    Dim conn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strSQL As String
   
    Dim strTemp As String
   
    Set conn = CurrentProject.Connection
    strSQL = "select * from [test table]"
    rst.Open strSQL, conn, adOpenDynamic, adLockOptimistic
   
    strTemp = rst!Text
   
    rst.Close
    Set rst = Nothing
    conn.Close
    Set conn = Nothing
   
End Sub

ok, that works if I have a linked table in Access.  I was trying to connect directly to the table on the sql server.  My code allows me to do that for tables with one word.  But not with two.  I am trying to avoid linking the tables, as this program runs thru many different databases ( 1 for each client).  My current program links the 10 tables that I need, extracts the information, and then links the tables from the next database and repeats.  The linking process is very slow.

Actually the only part that doesnt work is strTemp = rst!Text...that is probably a missing reference.  any idea which one so I can add it?  Again...many many thanks.

rst!Text actually refers to a column named Text in the table.  

I just put that in there to make sure that I was getting the information from the tbel.

This Works:

Sub hamster()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strcnn As String
    Set cnn = New ADODB.Connection
    strcnn = "DSN=WESTHEALTHCENT;UID=Admin;PWD=Password;"
    cnn.Open strcnn
    Set rst = New ADODB.Recordset
       
    rst.Open "patient", strcnn, adOpenDynamic, adLockOptimistic, adCmdTable
   
    rst.Close
    cnn.Close
    Set cnn = Nothing
    MsgBox "done!"
End Sub


This Does Not Work:

Sub hamster()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strcnn As String
    Set cnn = New ADODB.Connection
    strcnn = "DSN=WESTHEALTHCENT;UID=Admin;PWD=Password;"
    cnn.Open strcnn
    Set rst = New ADODB.Recordset
       
    rst.Open "billing detail", strcnn, adOpenDynamic, adLockOptimistic, adCmdTable
   
    rst.Close
    cnn.Close
    Set cnn = Nothing
    MsgBox "done!"
End Sub

We have tried:
"billing detail"
 [billing detail]
"[billing detail]"
'billing detail'
"'billing detail'"

Help and thanks:)

Did you try putting a sql string in place of the actual table name like in my example?

Yes we tried:
Sub hamster()
   
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strcnn As String
    Dim Strsql As String
    Set cnn = New ADODB.Connection
   
    Strsql = "Select * from [patient]"
   
    strcnn = "DSN=warrenWESTHEALTHCENT;UID=Admin;PWD=Password;"
    cnn.Open strcnn
    Set rst = New ADODB.Recordset
       
    rst.Open Strsql, strcnn, adOpenDynamic, adLockOptimistic, adCmdTable
       
    rst.Close
    cnn.Close
    Set cnn = Nothing
    MsgBox "done!"
End Sub

and we get:
run-time error '-2147217900 (80040e14)':
[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Syntax Error:select*from Select<<???>>* from [patient]

Take off the adcmdtable when using sql statement.

Sub hamster()
   
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strcnn As String
    Dim Strsql As String
    Set cnn = New ADODB.Connection
   
    Strsql = "select * from patient"
   
    strcnn = "DSN=warrenWESTHEALTHCENT;UID=Admin;PWD=Password;"
    cnn.Open strcnn
    Set rst = New ADODB.Recordset
       
    rst.Open Strsql, strcnn, adOpenDynamic, adLockOptimistic
       
    rst.Close
    cnn.Close
    Set cnn = Nothing
    MsgBox "done!"
End Sub

The above works great...but sub in billing detail for patient or [billing detail] or 'billing detail' and it bombs out again.

The only solution i can come up with so far that will make this work is to create a view of each of the tables with 2 names (billing_detail) and that seems like a ferocious waste!

I do not understand why that is not working.

I created a table on Sql Server with a space in the name and then linked it to an Access database and then using the code above was able to get the data from the table.

The only thing that I did different that I did not mention was that when I linked the table from Sql server I renamed the table.  I took the dbo_ off the front of the table name.

Maybe the table name is spelled wrong or the linked table still has the dob_ in front of the name from when it was added to the database.

Create a query in Access based on a linked table with a space in the name.  Look at the Sql syntax of that query what does it say for the table name?

This is exactly why it is not a good idea to use spaces in any object name.  Could you just change the table name or do you have a lot of other references to it?  and if you do have other references to it, are they working?  You would probably avoid a lot of future headaches.  It's just not good design practice to have any non-standard characters in an object name.

Hey guys, thanks for the replies.
 
1st Schlepuetz, yes that code does work if you have linked the table.  I had code working where if I either manually or with vb code linked all 10 tables I could address them with however many words were in the name.  I could also rename them at that point, making the need for dealing with two names moot.  But because I am working from a client system, needing access to 10 tables in like 30 different databases, the linking (& unlinking) process adds about 45 minutes to each time I run it.  I got my code to work with ado connections directly to the Pervasive database and it is much faster...except 3 of the 10 tables have at least two names!  I guess I am just going to create a single word named "view" of each of the tables and use that.  Thanks again for your help.  In a couple days, if I don't get a better answer, Ill award you the points for all of your hard work.
 
2nd Arji, I couldn't agree with you more!  But, unfortunately, the database that is in question is a proprietary one that was created by software that we are dependent on for now.  When I write code, I was taught to adhere to the same rules that you are referring to, and I have never regretted the extra time, energy and focus needed to keep things neat and making sense.
 
Thanks again peeps, Anyone else got any ideas on this before we close the chapter and move on to the work around?

I'm sure that's very frustrating for you.  

How exciting!  We asked a question elsewhere about server degridation from all the "views" we were going to create to address our two word table problem...and someone posted the correct way to do tables with two names!  chr$(34) & "billing detail" & chr$(34) does the deed!  So thanks again everyone for you help...and special thanks to RHansonLinea for coming up with a winner!  I am giddy...

chr$(34) & "billing detail" & chr$(34)

NEVER would have gueesed that one!

Excellent!  and interesting....one to remember

PAQ / Ref

Dave