Connecting Access Database with other software (1 Viewer)

Blancorn

Registered User.
Local time
Today, 09:27
Joined
Feb 27, 2016
Messages
30
Hello everyone.

At my workplace I was asked to connect my access database to our ERP system - in terms of retrieving data from that system.
I have quite good exprience in designing stand-alone applications in access, but I have no clue how can i retrieve data from other applications more advanced than excell or access files.

Could you please give me just a hint where to start looking for the solution?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:27
Joined
Oct 29, 2018
Messages
21,358
Hi. You could check with the vendor if this is possible and if so, how. Just a thought...
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:27
Joined
Jan 20, 2009
Messages
12,849
Determine if the type of database it uses as its backend and get the ODBC connector if available.
 

Blancorn

Registered User.
Local time
Today, 09:27
Joined
Feb 27, 2016
Messages
30
I agreed with the vendor that they prepare a SQL query which can retrieve data from their system. Unfortunately the code provided is not working properly when I paste it directly to MS Access query. Am I doing something wrong or the code is not correct?

While putting this code to the query I got the following messages:
1. Syntax error: CAST(k.BillNumCorrected as varchar(30)) as KOR_SYM
2. When I removed function CAST, then next message was: Syntax Error with FROM clause
3. When I remove all three JOINs, then the message error is the following: Cannot find file c:/users/admin/documents/vision.pub

Please find the initial code below. I will highly appreciate any hint why it is not working...

Code:
SELECT
f.RecordType RTYP
,f.TableRecID ID
,f.RecordStatus STAT
,f.CompNum as FIRMA
,f.PlantCode as STAN_SP
,f.DocType as KOREKTA
,CAST(k.BillNumCorrected as varchar(30)) as KOR_SYM
,CAST(f.OriginalInvCredNoteNumber as varchar(8)) as ORYG_SY
,b.BillAddrNum as UNIK_ID
,c.GLCode as TYP
,CAST(f.DocNum as varchar(8)) as NK
,f.CurrCode as WAL
,CAST(b.Rate as real) as KRS
,f.TaxDecDate as D
,f.DocDate as DW
,f.DueDate as TZ
,f.TermsCode as PL
,CAST(f.DiscPercent as real) as RAB
,c.BillLineNum as LP
,c.ItemCode as KTM_1
,c.ItemCode as KTM
,c.BillCode as KTM_U
,CAST(c.PriceUnitConv as real) as QMDC
,CAST(c.BillQty as real) as IL
,CAST(c.FCBillValue as real) as WWAL
,CAST(c.FCBillTaxValue as real) as VWAL
,CAST(c.BillQty as real) as WN
,CAST(c.FCUnitPrice as real) as CPR
,CAST(c.BillTaxValue as real) as WV
,c.TaxCode as SV
,c.ShipReqNum as POWOD
,'1 ' as HAN
,1 as K_LP
,'N' as STATUS
,'desc' as OPIS
from VISION.PUB.PV_XLSLInvoice as f
join VISION.PUB.PV_Bill as b on ( f.PlantCode=b.PlantCode and f.CompNum=b.CompNum and f.DocType=b.Credit and f.DocNum=b.BillNum )
join VISION.PUB.PV_BillLine as c on ( b.PlantCode=c.PlantCode and b.CompNum=c.CompNum
and b.BillNum=c.BillNum)
join VISION.PUB.PV_BillReq as k on ( c.PlantCode=k.PlantCode and c.CompNum=k.CompNum and c.BillLineNum=k.BillLineNum
and c.BillNum=k.BillNum)
where f.Direction=0 and f.RecordStatus=0
and f.DocType=0
order by 2
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:27
Joined
Oct 29, 2018
Messages
21,358
Hi. Using an SQL statement alone may not work if you don’t have a connection established with the ERP database. Perhaps they meant for you to execute the SQL statement in the ERP system. Did they tell you that you can connect your Access database to the ERP?
 

Blancorn

Registered User.
Local time
Today, 09:27
Joined
Feb 27, 2016
Messages
30
They told me that connection is already established and is done not directly to ERP database but through the "bridge table" - I assume it is VISION.PUB.

Maybe you can help me to ask some questions to this vendor so they can provide whatever is needed?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:27
Joined
Oct 29, 2018
Messages
21,358
They told me that connection is already established and is done not directly to ERP database but through the "bridge table" - I assume it is VISION.PUB.

Maybe you can help me to ask some questions to this vendor so they can provide whatever is needed?

Can you post a screenshot of your Navigation Pane showing the tables?
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:27
Joined
Jan 20, 2009
Messages
12,849
Are you putting it in a PassThrough query?
 

Blancorn

Registered User.
Local time
Today, 09:27
Joined
Feb 27, 2016
Messages
30
Are you putting it in a PassThrough query?

Honestly, I don't know what are PassThrough queries and do how they work... I'm a newbie in this topic.

Can you see my files posted in previous message?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:27
Joined
Oct 29, 2018
Messages
21,358

Hi. I’m on my phone and don’t see any image or attachment.
 

Blancorn

Registered User.
Local time
Today, 09:27
Joined
Feb 27, 2016
Messages
30
You are right.
How can I add this ERP table to my database?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:27
Joined
Oct 29, 2018
Messages
21,358
You are right.
How can I add this ERP table to my database?

Hi. So, if you don’t have a connection to the ERP tables yet, then the SQL statement they gave you won’t work yet. To connect Access to your ERP, you may need three things.

1. The ERP has to allow external connections
2. Your machine must have an appropriate ODBC driver or whatever driver the ERP needs for external connections
3. The correct Connection String for Access to connect to the ERP

If you go to the External tab on the Ribbon, you might get lucky and be able to connect to the ERP.
 

June7

AWF VIP
Local time
Today, 08:27
Joined
Mar 9, 2014
Messages
5,423
Sounds like incorrect or incomplete info from vendor. They gave you a query statement but did not advise how to connect to their data. Presuming the app is split design, have you determined what they use as backend? My guess is it is some flavor of SQLServer or MySQL.

I worked with a proprietary app that had split design. The GUI frontend was an executable and I have no idea what it was built with. The backend was just an MDB file. I built an Access frontend that I could connect directly to the MDB file tables to read data (never edit).

So where is this 'bridge' table located? It doesn't show in the image. If vendor is expecting you can add that query to their frontend, they should tell you how.

I don't think Access supports Cast() function. https://support.office.com/en-us/ar...unctions-8EBB0E94-2D43-4975-BB13-87AC8D1A2202
 
Last edited:

Blancorn

Registered User.
Local time
Today, 09:27
Joined
Feb 27, 2016
Messages
30
Thank you all for the advises - we are slowly coming to the end.

Vendor told me that I should refer to the ODBC which is already configured.
They advised me to follow this instruction:

support.office.com/en-us/article/import-or-link-to-data-in-an-sql-server-database-a5a3b4eb-57b9-45a0-b732-77bc6089b84e

For time being I got stuck at the point 2. Create DNS file, because on the available DNS drivers list, ODBC Driver 13 for SQL Server doesn't exist. Do you have any clue why?

Should I ask vendor for an additional information?

Thank you in advance.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:27
Joined
Jan 20, 2009
Messages
12,849
For time being I got stuck at the point 2. Create DNS file, because on the available DNS drivers list, ODBC Driver 13 for SQL Server doesn't exist. Do you have any clue why?

Because it is not installed on your PC. You should be able to get it from Microsoft.
 

Blancorn

Registered User.
Local time
Today, 09:27
Joined
Feb 27, 2016
Messages
30
Thank you all for the support.
We are very close to the happy end.

I have DSN file and can see table list from desired database.

Unfortunatelly when I choose tables to be imported/connected I get another error:
"Reserved error (-7748); there is no message for this error"

Do you have any clue how to manage this?
 

Blancorn

Registered User.
Local time
Today, 09:27
Joined
Feb 27, 2016
Messages
30
Thank you for this link.
I was trying to apply the fix described there:

Fixes For “Reserved Error -7748” in MS Access 2003, 2007, and 2010
You must keep a proper backup of the registry before making any changes.

In order to resolve this error, set WorkArounds2=8192 for the affected data source. This can be done by using the Registry Editor (RegEdit).

1. Run RegEdit

2. Search the ODBC DSN in the Registry:

For a USER Data Source, go to HKEY_CURRENT_USER -> Software -> ODBC -> ODBC.INI -> YourDataSourceName

For a SYSTEM Data Source, go to HKEY_LOCAL_MACHINE -> Software -> ODBC -> ODBC.INI -> YourDataSourceName

For 32-bit applications (ODBC drivers) on 64-bit Windows, (MACHINE) Data Source, go to

HKEY_LOCAL_MACHINE -> Software -> Wow6432Node -> ODBC -> ODBC.INI -> YourDataSourceName

3. tap to the folder for your data source, from the menu select:

Edit -> New -> String Value

4. Assigned name to the new string value to be WorkArounds2

5. Make a double tap on the WorkArounds2 in the right hand panel as this will opens up a dialog box with the data value field.

6. Change the data value to 8192 (The string value that must be added to Windows XP SP2 and Progress 10.1A02 is WorkArounds2=40960)

7. Click OK

8. Close RegEdit

If you are using Windows 95 and 98 then you need to reboot, then only the change done in settings will get into effect.

Unfortunatelly I cannot find my DSN file in the registry (see screen attached).


Do you know what should I do, to see my DNS there?
 

Attachments

  • Rejestr.jpg
    Rejestr.jpg
    97.7 KB · Views: 239

Users who are viewing this thread

Top Bottom