Hi everyone - I have a problem

[bultimatem]

Hi everyone -

I have a problem accessing a table in my database from excel. When I run the sub procedure below, I get an error, presumably because I have a space in my table name. Is there any way to "fool" the computer into thinking of the table name as a whole rather than just as "Program"? I would change the table name except for the fact that our web based GUI relies on that name.


Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Dim table As String

table = "Program Information"
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=\\sharedComp\folder\myDB.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open table, cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("ProgramID&quot = "BLA"

Thanks for your help.


Bryan Marble
IEWS
BAE Systems

 

[bultimatem]

Sorry about the generic heading, I forgot to specify it when I was writing the post.
:

Bryan Marble
IEWS
BAE Systems

 

[SkipVought]

Bryan,

Just a shot in the dark -- try tics...

    table = "'Program Information'"

???

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[RoyVidar]

Hi!

strTable = "[Program Information]"

Table might be one of the reserved words in access/VBA (it's a property) and brackets might work to recognize it as a table.

HTH Roy-Vidar