UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Postgres - Currentdb.execute - Could Not Find File, Access 2013    
 
   
amaklop
post Nov 6 2018, 08:34 AM
Post#1



Posts: 17
Joined: 23-September 18



Hi

I'm not sure if I'm posting on the correct forum. This is continuing from a previous post which became a different issue to the one I originally posted.

I am trying to connect to a PostGresql database and insert from one table to another, but I am getting the following error:

Could not find file 'c:\Users\Username\Documents\MAT.mdb'

I'm also not sure how to reference my Postgresql tables in VB. When I reference them with DBeaver I need to select from "MAT"."QUOTES" but when I write a query in access I need to use MAT_QUOTES. So what do I do in VB?

Here is my code:


CODE
Dim SQL As String
  Dim Status As String
  Dim Db As DAO.Database
  
  Status = Me!Status
  
  Set Db = DAO.OpenDatabase("Matrix", , "ODBC;DSN=PostgreSQL35W;DATABASE=Matrix;SERVER=localhost;PORT=5432;CA=d;A7=10
0;B0=255;B1=8190;BI=0;C2=;CX=1c305008b;A1=7.4")
  
  If Status <> "Quoted" Then
     MsgBox "You can only generate an invoice from a Quote that has been finalised"
     Exit Sub
  Else
      SQL = "insert into MAT_PROFORMA (CUSTOMER_ID,CUST_ORDER_DATE,REFERENCE_NO,USER_ID,NOTES,STATUS,QUOTE_ID,EMAIL
_ADDRESS,CONTACT_NAME,JOB_NO)" & _
            "  SELECT CUSTOMER_ID, QUOTE_DATE, REFERENCE_NO, USER_ID, NOTES, 'New', QUOTE_ID, EMAIL_ADDRESS, CONTACT_PERSON, JOB_NO  " & _
            "  FROM MAT.QUOTES WHERE QUOTE_ID = " & QUOTE_ID & ";"
      CurrentDb.Execute SQL, dbFailOnError
    
      SQL = "INSERT INTO MAT.PROFORMA_LINES (PRODUCT_ID, PRICE, QUANTITY, DISCOUNT,NETT_AMT, DISPLAY_ORDER, ITEM_NO )" & _
            "  SELECT PRODUCT_ID, PRICE, QUANTITY, DISCOUNT, NETT, DISPLAY_ORDER, ITEM_NO FROM MAT.QUOTE_LINES WHERE QUOTE_ID = " & QUOTE_ID & ";"
      CurrentDb.Execute SQL, dbFailOnError
  End If

Go to the top of the page
 
cheekybuddha
post Nov 6 2018, 08:46 AM
Post#2


UtterAccess VIP
Posts: 10,548
Joined: 6-December 03
From: Telegraph Hill


Have you already linked tables in Access?

I guess you have when you mention:
>> but when I write a query in access I need to use MAT_QUOTES <<

If so, you don't have to bother opening a link to the PG database in code.

Otherwise, try the code as you have it in your post, but substitute CurrentDb with your Db variable, and also use the real table names/references (e.g. MAT.PROFORMA)

like:
CODE
' ...
      SQL = "insert into MAT.PROFORMA (CUSTOMER_ID,CUST_ORDER_DATE,REFERENCE_NO,USER_ID,NOTES,STATUS,QUOTE_ID,EMAIL
_ADDRESS,CONTACT_NAME,JOB_NO)" & _
            "  SELECT CUSTOMER_ID, QUOTE_DATE, REFERENCE_NO, USER_ID, NOTES, 'New', QUOTE_ID, EMAIL_ADDRESS, CONTACT_PERSON, JOB_NO  " & _
            "  FROM MAT.QUOTES WHERE QUOTE_ID = " & QUOTE_ID & ";"
      Db.Execute SQL, dbFailOnError
' ...

--------------------


Regards,

David Marten
Go to the top of the page
 
amaklop
post Nov 7 2018, 01:27 PM
Post#3



Posts: 17
Joined: 23-September 18



Thanks for your help. I am still battling frown.gif I have decided to keep MS Access as my backend for now. I'll try Postgres again later when I have more time to investigate issues properly
Go to the top of the page
 
cheekybuddha
post Nov 9 2018, 09:16 AM
Post#4


UtterAccess VIP
Posts: 10,548
Joined: 6-December 03
From: Telegraph Hill


OK, getting to grips with alternate backends takes a little time!

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th November 2018 - 12:34 PM