Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access to SQL Server (Access 2K, Win 2KPro, SQL Server 2K)

    <font color=red>I tried linking a re-written query in SQL Server to the form used to display info to the table and now it is giving me write errors (like dirty writes) and won't let me save the changes. ?? I suppose SQL Server is confused by Access trying to write to it's tables via a linked table.... This whole project is starting to look a little doomed. I suppose I will have to drop back a bit as it is starting to look difficult to hook Access back to SQL Server in 'real time'. Looks like batch loads will have to happen. <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22> Anyhow, when tables are linked into SQL Server, searches take _forever_</font color=red>

    I have been working on resolving some issues with Access/SQl Server and have come to the conclusion that if I choose to use Access as a front end to SQL Server, it's best to keep any new info/tables on SQL Server rather than in Access itself (since, in this case, I will be building some web-based reporting on info stored in what is, at the moment, a table built solely in Access.

    So! I thought I'd ask you guys how one runs ADO connects in Access to write to a table in SQL Server from Access...

    In particulare, I have this set of code that currently runs in Access to build a particular table:
    <font face="Georgia">
    Dim strSQL As String
    Dim strSQL2 As String
    Dim txtTIN As String
    Dim txtDate As String
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim Entity_Name As String

    txtTIN = Me.Entity_TIN.Value
    txtDate = Me.Entity_FY.Value

    Set cnn = CurrentProject.Connection
    </font face=georgia>
    I suspect that the CurrrentProject.Connection string needs to be changed into something that goes out to SQL Server instead of an object in Access.

    Any ideas? I will be researching this after posting so I might have a solution soon enough, but it's always good to ask you guys.

    tia
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Access to SQL Server (Access 2K, Win 2KPro, SQL Server 2K)

    An example connection string would be:
    <code>"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbname;Data Source=servername"</code>
    this assumes you are using Windows security rather than SQL Server security and you can change dbname and servername as appropriate.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to SQL Server (Access 2K, Win 2KPro, SQL Server 2K)

    thanks for the tip. however, I keep running into problems with trying to re-tie Access tables back into SQL Server and am now resigned to setting up a batch load process instead. It would be great if users on the intended website could run the batch load by clicking a button, but I ain't holding my breath over that. at the moment, it is starting to look like one of those weekly tasks in my Outlook Tasks dealy-bopper.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •