Results 1 to 5 of 5
  1. #1
    sirrahhc is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    3

    Connect to SQL w/ Access and INSERT records

    Hey all, hope everyone is well. I have a dilema that I hope you guys can help me with. I have created an Access database (that I am currently running from my workstation) that has multple linked tables to a SQL DB on a stand-alone server. I have a form that I allow users to enter information in several text boxes and combo boxes. When the user hits the 'Enter' button, I want to insert these records into a table in a SQL DB.

    I am getting a "Run-time error '3024': Could not find output table 'ProjectNotes'.

    I believe I have some connection info incorrect in my VB code within the form but cannot figure out what it is. My code is below:

    Private Sub cmdEnter_Click()

    Dim adoConn As New ADODB.Connection
    Dim connString As String

    connString = "Driver={SQL Server};Server=10.20.100.22;Database=TestDatabase; Uid=TestUserID;Pwd=TestPass;"
    adoConn.ConnectionString = connString
    adoConn.Open connString

    Dim strSQL As String
    Dim strProjectTitle As String
    Dim strNotes As String
    Dim strResponsibleProf As String
    Dim strAddingDateTime As String
    Dim strTargetedCompletionDate As String
    Dim strNotesEnteredBy As String
    Dim strMeetingTitle As String

    'set variable values
    strProjectTitle = Me.cboProject.Value
    strNotes = Me.txtNotes.Value
    strResponsibleProf = Me.cboRespProf.Value
    strAddingDateTime = Me.txtAddingDateTime.Value
    strTargetedCompletionDate = Me.txtTargetedCompletionDate.Value


    strNotesEnteredBy = Me.cboNotesEnteredBy.Value
    strMeetingTitle = Me.cboMeeting.Value

    strSQL = "Insert Into ProjectNotes ([ProjectTitle],[Notes],[ResponsibleProfessional],[AddingDateTime],[TargetedCompletionDate],[NotesEnteredBy],[MeetingTitle]) Values ('" & strProjectTitle & "','" & strNotes & "','" & strResponsibleProf & "'," & strAddingDateTime & "," & strTargetedCompletionDate & ",'" & strNotesEnteredBy & "','" & strMeetingTitle & "');"

    DoCmd.RunSQL strSQL

    End Sub


    ...Can anyone tell from a quick glance what I am doing incorrectly here? I have been playing with this for about a week now and have used what I could from online forums and sources but am now stuck on this error. I appreciatte any help in advance, thanks!!!!!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,127
    Well, at a glance, the DoCmd is not using the ADO connection. DoCmd would require the tables to be linked, which I assume they're not. It's not how I've done it, but try this untested bit instead of the DoCmd line:

    adoConn.Execute strSQL
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    sirrahhc is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    3
    perfect!!! thanks so much!!!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,127
    Ah, glad it worked for you. Welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    sirrahhc is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    3
    good to be here, thanks again!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 12-13-2010, 11:49 PM
  2. Replies: 1
    Last Post: 09-26-2010, 04:17 PM
  3. Replies: 5
    Last Post: 03-29-2009, 07:20 AM
  4. how to connect ms access to sql
    By marianne in forum Access
    Replies: 22
    Last Post: 03-24-2009, 11:14 AM
  5. connect from access to sql
    By kathyc in forum Programming
    Replies: 0
    Last Post: 09-28-2008, 12:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums