Results 1 to 5 of 5
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    Write SQL in Access Form FE to database BE

    Hi Guys,



    i want to for example create table in database, it is possible to write SQL in Access Form
    and pass it to database?

    Best,
    Jacek

  2. #2
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    I tried to use pass through query with returns records set to "No" but i am getting error:

    Click image for larger version. 

Name:	Screenshot_35.jpg 
Views:	12 
Size:	105.2 KB 
ID:	34697

    When statement is "select * from t_history" and returns records is set up to "Yes" this is working fine.

    Jacek

  3. #3
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm not sure this is possible.
    Have a read here for some pointers https://access-programmers.co.uk/for...d.php?t=295621
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi Minty,

    so this is not what i want to.

    Because i thought that i can create commands in easily way directly form Access.
    And i am seeing that i ahve to do workarounds.

    Like input text in Access, then
    1. Create a temp sql file
    2. Run this file using VBA
    3. Delete sql file
    4. Save statement in table...

    Best,
    Jacek

  5. #5
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi,

    i find code to do it.
    You do no have to create sql in seperate files, you can use command function in adodb properties.

    Code:
    Sub ExecuteSqlScript()
     
        Dim Script As String
        Dim FileNumber As Integer
        Dim Delimiter As String
        Dim aSubscript() As String
        Dim Subscript As String
        Dim i As Long
        Dim cn As Object
        Dim comm As Object
        Dim rs As Object
        Dim dbConnectStr As String
        Dim strSheet As String
        Dim strRange As String
    
    
        Set cn = CreateObject("ADODB.Connection")
        Set comm = CreateObject("ADODB.Command")
        Set rs = CreateObject("ADODB.Recordset")
    
    
    username = GetUsername
    pass = getPassword
        
    strConnect = "DRIVER={PostgreSQL Unicode};" & _
    "DATABASE=AccessLog;" & _
    "SERVER=localhost;" & _
    "PORT=5432;" & _
    "UID=" & username & ";" & _
    "PWD=" & pass & ";" & _
    "Trusted_Connection=Yes;"
    
    
    
    
        cn.ConnectionString = strConnect
        
        On Error Resume Next
        cn.Open
        If Err.Number <> 0 Then
            MsgBox Err.Description
            Exit Sub
        End If
        On Error GoTo 0
    
    
        Set comm.ActiveConnection = cn
        rs.ActiveConnection = cn
    
    
        Delimiter = ";"
        FileNumber = FreeFile
        
        Script = "CREATE TABLE public.eventcheck(i int);"
     ''   Script = String(FileLen(FilePath), vbNullChar)
     
        ' Grab the scripts inside the file
    ''    Open FilePath For Binary As #FileNumber
    ''    Get #FileNumber, , Script
    ''    Close #FileNumber
     
            comm.CommandText = Script
            comm.CommandTimeout = 0
            rs.Open comm
            
        cn.Close
     
    End Sub
    Best,
    Jacek

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

Similar Threads

  1. Macro to write data in database for 6 different employees
    By ManipulatoRX in forum Programming
    Replies: 5
    Last Post: 01-10-2018, 10:27 AM
  2. Replies: 3
    Last Post: 10-05-2015, 01:21 PM
  3. Replies: 2
    Last Post: 08-01-2014, 09:29 PM
  4. Replies: 24
    Last Post: 07-08-2013, 12:59 PM
  5. C program: read/write Access database
    By serendipity1276 in forum Programming
    Replies: 1
    Last Post: 08-15-2011, 01:15 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