Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2007
    Posts
    348

    Unanswered: Insert from Access to MSSQL get Run time error 3192

    Hey all,
    It's quite a while since I've had cause to pass through this way. Today, though, I am stumped and hoping I can get some help. I have an unbound form in Access that I want to use to send data to a MS SQL server table. The table is called DrugTbl. Whenever I attempt to send the data I get the message "Run-time error '3192': Could not find output table 'DrugTbl'. I used the same connection and can query the table well enough, I just can't seem to insert. I'm sure it's something I'm just overlooking. I have checked and double checked the table name. Any thoughts?
    Thanks for taking some time to even consider it.
    I'll post the code below:

    Code:
    Private Sub NotifyCmd_Click()
    
    If Not (Me.BarcodeTxt = "" Or IsNull(Me.BarcodeTxt) Or Me.GenericNameTxt = "" Or IsNull(Me.GenericNameTxt) Or Me.StrengthTxt = "" Or IsNull(Me.StrengthTxt) Or Me.StrengthDescriptionCmb = "" Or IsNull(Me.StrengthDescriptionCmb)) Then
        Dim conn As ADODB.Connection
        Dim sql As String
        '''test'
        Dim testRs As ADODB.Recordset
        
        
        Set conn = New ADODB.Connection
        conn.Open "Provider=SQLOLEDB;Data Source=jhphsql1;Initial Catalog=orMedList;Integrated Security=SSPI;"
        sql = "SELECT  * FROM drugtbl;"
        Set testRs = New ADODB.Recordset
        testRs.Open sql, conn
        Do While Not testRs.EOF
        Debug.Print testRs![genericname]
        testRs.MoveNext
        Loop
        MsgBox "done", vbCritical
        
        sql = "INSERT INTO DrugTbl (Barcode,GenericName,Strength,StrengthDescription,Volume,VolumeDescription,Form,Manufacturer,BrandName,ORapproved) VALUES('" & Me.BarcodeTxt & "','" & Me.GenericNameTxt & "'," & Me.StrengthTxt & ",'" & Me.StrengthDescriptionCmb & "'," & Me.VolumeTxt & ",'" & Me.VolumeDescriptionCmb & "','" & Me.FormCmb & "','" & Me.ManufacturerCmb & "','" & Me.BrandNameTxt & "', 0 );"
        'sql = "INSERT INTO [DrugTbl] ([Barcode],[GenericName],[Strength],[StrengthDescription]) VALUES ('test','test',30,'mg')"
        
        Debug.Print sql
    
        DoCmd****nSQL sql
        DoCmd.SendObject acSendForm, BarcodeScannerFrm, acFormatXLS, "pharmis@jhmi.edu", , , "test", "test", False
        conn.Close
        
        Call ClearCmd_Click
    Else
        MsgBox "You must make sure you fill in the Barcode, Generic Name, Strength and Strength Description of all new drugs, at a bare minimum", vbCritical, "Input Error"
        
    End If
    End Sub

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I assume the table isn't linked? The DoCmd line isn't using the ADO connection, it's looking for a local or linked table. Since you have a recordset open, I'd use the AddNew method to add your record.
    Paul

  3. #3
    Join Date
    Feb 2007
    Posts
    348
    Paul,

    In the course of my mental thrashings about I did link the database and found it worked ok but I have a concern that I plan to test tomorrow; I'm not going to be the user of this database and I don't know if I'll be able to add the ODBC connection to the workstations where it will be used. I assumed that if I used the linked tables, I would need the ODBC connection in the system. I could be way off base on that. It's my first time with MS SQL and MS Access together.
    I played, for a very short time, with addnew but wasn't having any luck with it but I'm certain that was just because I haven't used it before. Depending on how the ODBC experimenting goes tomorrow, perhaps that'll be my route.
    Thanks for your speedy response, I appreciate it.

    Mike

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I assume they're on a network? I typically link to SQL Server tables using a File DSN created and stored on the server. That way, I don't have to put anything on individual PC's. It will work for any PC on the network.

    The AddNew should work, so post back with what's going wrong if you decide to go that way and can't get it to work.
    Paul

  5. #5
    Join Date
    Feb 2007
    Posts
    348
    Correct on the network. I'll play with the connections tomorrow. I'll also get you details on the add new. Off the top of my head, it was something to the effect of "the field named | could not be found". It was the "|" that really confused me. It appears to the the OR bar not a lower case L or a capital i. I'll get back with code and detail tomorrow. Again, thanks Paul.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem, Mike. We'll get it sorted out.
    Paul

  7. #7
    Join Date
    Feb 2007
    Posts
    348
    Paul,
    Sorry I didn't get back to you yesterday. I had two days to crank out this app and I needed yesterday to sand out all the rough edges of the initial version. I was able to take advantage of linked tables to add data. Below is the code as it settled out:

    Code:
    Private Sub NotifyCmd_Click()
    
    Me.StrengthTxt = validateForNumbers(Me.StrengthTxt)
    Me.VolumeTxt = validateForNumbers(Me.VolumeTxt)
    If Not (Me.BarcodeTxt = "" Or IsNull(Me.BarcodeTxt) Or Me.GenericNameTxt = "" Or IsNull(Me.GenericNameTxt) Or Me.StrengthTxt = "" Or IsNull(Me.StrengthTxt) Or Me.StrengthTxt = "0" Or Me.StrengthDescriptionCmb = "" Or IsNull(Me.StrengthDescriptionCmb)) Then
        Dim sql As String
        DoCmd.SetWarnings False
        sql = "INSERT INTO dbo_DrugTbl (Barcode,GenericName,Strength,StrengthDescription,Volume,VolumeDescription,Form,Manufacturer,BrandName,ORapproved) VALUES('" & Me.BarcodeTxt & "','" & Me.GenericNameTxt & "'," & Me.StrengthTxt & ",'" & Me.StrengthDescriptionCmb & "'," & Me.VolumeTxt & ",'" & Me.VolumeDescriptionCmb & "','" & Me.FormCmb & "','" & Me.ManufacturerCmb & "','" & Me.BrandNameTxt & "', 0 );"
        DoCmd.SetWarnings True
        
        Debug.Print sql
        DoCmd.SetWarnings False
        DoCmd****nSQL sql
        DoCmd.SetWarnings True
        Me.StrengthDescriptionCmb.Requery
        Me.VolumeDescriptionCmb.Requery
        Me.FormCmb.Requery
        Me.ManufacturerCmb.Requery
        
        Call ClearCmd_Click
    Else
        MsgBox "You must make sure you fill in the Barcode, Generic Name, Strength and Strength Description of all new drugs, at a bare minimum", vbCritical, "Input Error"
        
    End If
    End Sub
    May I ask, best practices-wise, how should I be handling this kind of thing. I do a lot of web interface with the SQL server but there's only one way for me to handle the connection there. Here it appears there are about 3. Are linked tables the way to go?
    Also, since I'm picking your brain, for users, I'm adding them by going into SQL server and adding each to the server as they come up. Is there a better way to handle this?

    Thanks very much for your help in getting me pointed down the path of just using linked tables so I could get this done on time.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, there are very knowledgeable users who use unbound forms and stored procedures, or who use ADP's. That said, the generally accepted MS standard is linked tables. Personally I usually use forms bound to linked SQL Server tables. That all assumes a networked environment. I have a couple of applications that need to work over a WAN (off-site users connecting to the network via VPN). I used mainly unbound forms and ADO recordsets or commands with stored procedures so that I could really control what was going over the wire. I'm happy to say that the speed difference between networked users and off-site users is negligible.

    I'm far from an expert on SQL Server security. I usually use a single SQL Server user, with the login either saved with the linked tables or saved in code, so users don't need to log in. I'm in an environment that doesn't really require high security though. I sometimes create my own login process ("roll-your-own" security), but it's more for audit trail purposes than security, and only when users won't be logging into Windows with their own ID's that I could capture. I know some people use Windows Authentication with SQL Server, which might be an option for you as well.
    Paul

Posting Permissions

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