SQL connection using ODBC

[Malcy]

Hi
For one database I am looking at I need to hold main tables in SQL but link through to Access using ODBC.
I have not really done this before and am toiling a bit.
I kept getting a fail and after searching came up with the following code (there are bits before and after but I think this is the problematic bit. The first strCnn is commented out since it didn't work,

' Define connection
'    strCnn = "Driver ={SQL Server}; Server = Iona; Database = ElcapTimesheet; Trusted_Connection=yes;"
    strCnn = "DSN=ElcapBe;Uid=Malcolm;Pwd=;"
    
' Set the connections
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
' Open the connection
    cnn.Open strCnn
    
' Append data to dbo_tbl-timesheet
        rst.Open "dbo_tbl-timesheet", cnn, adOpenDynamic, adLockOptimistic
            With rst
                Select Case Me.cboActivity
                    Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 14, 15
                        .AddNew
                            .Fields("strTsEmp") = Me.cboStaff
                            .Fields("dtmTsDate") = Me.txtDate
                            .Fields("dtmTsStart") = Me.txtStart
                            .Fields("dtmTsDate1") = Me.txtDate1
                            .Fields("dtmTsEnd") = Me.txtEnd
                            .Fields("lngTsAct") = Me.cboActivity
                            .Fields("lngTsPayCode") = Me.cboPayCode
                            .Fields("strTsSu") = Me.cboServUser
                        .Update

I am getting an error message on the rst.open line. The message alludes to an incorrect syntax near 'timesheet'
Is this an error in my strCnn expression or is it something in the table I set up in the SQL Server database?
I am also a little perplexed since I call the table tbl-timesheet in SQL but within Access the linked table becomes dbo_tbl-timesheet. I assumed (perhaps wrongly) that since this VBA was in Access it would want the Access name for the linked table.
I have no problem seeing the table data from Access and have run a couple of update and append queries to the linked tables without issue.
If anyone can point out what I have got wrong here I would be most appreciative.
If I cannot get ADO to append the data then I could setup append queries I think.
Thanks and best wishes

[KenHigg]

Here's a stab in the dark; It may not like the '-' in 'dbo_tbl-timesheet'.

???

[Malcy]

Thanks Ken
Well, removing the hyphen certainly moved it on but hasn't sorted it sadly. I renamed the SQL table to timesheet and then changed the rst.open line to

        rst.Open "dbo_timesheet", cnn, adOpenDynamic, adLockOptimistic

I am now getting an error message
[Microsoft][ODBC SQL Server Driver][SQL Server] Could not find stored procedure "dbo_timesheet"

Does this make sense to anyone???

[KenHigg]

It seems as though it can't find the recordsource. Is 'dbo_timesheet' the name of a table or sp on your sql server?

[Malcy]

Hi Ken
dbo_timesheet is an ODBC linkage from Access to my SQL Server where timesheet is set up in tables. I have just double checked the properties and it is a table. The file group is PRIMARY and the flows seems to equal 2
Does dbo mean "database owner"?
There are, as yet, no added stored procedures in the SQL database since I haven't worked them out yet!

[KenHigg]

Hum... Is there a reason why you are not linking to the table through the odbc administrator instead of trhough code a run time or can you already see the table in the Access database window?

[Malcy]

Hi
Please bear with me!
I can see the SQL table in the tables tab of my Access database having done an ODBC link. There seems to be no problem manually adding records to it if I just open it and type in relevant data.
This code is designed to append data from a data entry form in the Access front end. I have not had problems linking to Access tables in a backend database and assumed you used same technique for appending the form's data entry into the SQL tables.
Could be I am wrong - wont be the first and certainly wont be the last!
Is there a better way to do it? Such as using an append query.
How would I connect through odbc administrator - assuming it is easy to point me rather than have to write a long screed?
Thanks for your help and patience!
Best wishes

[KenHigg]

Can't you simply do a form and use the sql table as the recordsource?

[WayneRyan]

Malcy,

I'm a bit confused here.

Your example seems OK if you are using ADO to add records to an
unlinked table on the server. BUT, I'd remove the DSN reference
and the Uid/Pwd. The DSN isn't really needed, and the Uid/Pwd
isn't needed as long as the Windows username is legitimate on
the server.

Since it's a linked table, why not bind it directly to the form
and not even use a recordset?

If I had to use a recordset:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordSet("Select * From Linked_Table_Name")
rst.AddNew
rst!strTsEmp = Me.cboStaff
rst!OtherFields = ...
rst.Update

Wayne

[Malcy]

OK guys
Perhaps I would be better using the linked table bound to the form but if so, how do I bring date and time fields together to give a single date/time field in the table?
It is easier for data entry to have date and time in separate fields but SQL smalldatetime format seems to need to record as 31/12/2005 10:55:00
If using recordset I could concatenate either as textboxes or as variables but if not using recordset I am not sure how?
Ironically I started off years ago linking tables then moved away finding recordsets more flexible but have got stumped this time.
Any thoughts most appreciated
Thanks

[KenHigg]

Can you get the text box input mask to help you with this?

[WayneRyan]

Malcy,

If not, you can always use the AfterUpdate (or BeforeUpdate) of unbound
textboxes to make the Date/Time:

Me.HiddenDateTime = CDate(Me.txtDate & " " & Me.txtTime)

Gotta be easier than recordsets.

Wayne

[Malcy]

Hi Guys
Thanks for the suggestions.
I ended using Wayne's suggestion which worked a treat. Intriguingly I found hooking up the form far more difficult than doing it as ADO but guess it is all down to what you are used to.
At least this way it gets around that connection issue.
Thanks again and all best wishes for the New Year. Hope you both have a good one.