Go Back   Access World Forums > Apps and Windows > Other Software

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-23-2019, 05:43 PM   #1
Blancorn
Newly Registered User
 
Join Date: Feb 2016
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Blancorn is on a distinguished road
Connecting Access Database with other software

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?

Blancorn is offline   Reply With Quote
Old 02-23-2019, 06:18 PM   #2
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,552
Thanks: 29
Thanked 631 Times in 614 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Connecting Access Database with other software

Hi. You could check with the vendor if this is possible and if so, how. Just a thought...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 02-23-2019, 11:23 PM   #3
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,494
Thanks: 85
Thanked 1,459 Times in 1,377 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Connecting Access Database with other software

Determine if the type of database it uses as its backend and get the ODBC connector if available.

Galaxiom is offline   Reply With Quote
Old 06-02-2019, 03:50 PM   #4
Blancorn
Newly Registered User
 
Join Date: Feb 2016
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Blancorn is on a distinguished road
Re: Connecting Access Database with other software

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
Blancorn is offline   Reply With Quote
Old 06-02-2019, 03:59 PM   #5
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,552
Thanks: 29
Thanked 631 Times in 614 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Connecting Access Database with other software

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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-02-2019, 04:06 PM   #6
Blancorn
Newly Registered User
 
Join Date: Feb 2016
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Blancorn is on a distinguished road
Re: Connecting Access Database with other software

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?
Blancorn is offline   Reply With Quote
Old 06-02-2019, 04:15 PM   #7
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,552
Thanks: 29
Thanked 631 Times in 614 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Connecting Access Database with other software

Quote:
Originally Posted by Blancorn View Post
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?

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-02-2019, 04:19 PM   #8
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,494
Thanks: 85
Thanked 1,459 Times in 1,377 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Connecting Access Database with other software

Are you putting it in a PassThrough query?
Galaxiom is offline   Reply With Quote
Old 06-02-2019, 04:29 PM   #9
Blancorn
Newly Registered User
 
Join Date: Feb 2016
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Blancorn is on a distinguished road
Re: Connecting Access Database with other software

Quote:
Originally Posted by Galaxiom View Post
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?
Blancorn is offline   Reply With Quote
Old 06-02-2019, 04:41 PM   #10
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,552
Thanks: 29
Thanked 631 Times in 614 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Connecting Access Database with other software

Quote:
Originally Posted by Blancorn View Post
Hi. I知 on my phone and don稚 see any image or attachment.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-02-2019, 05:05 PM   #11
Blancorn
Newly Registered User
 
Join Date: Feb 2016
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Blancorn is on a distinguished road
Re: Connecting Access Database with other software

Please try this: imgur.com/a/g9dMivi
Blancorn is offline   Reply With Quote
Old 06-02-2019, 05:09 PM   #12
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,552
Thanks: 29
Thanked 631 Times in 614 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Connecting Access Database with other software

Quote:
Originally Posted by Blancorn View Post
Please try this: imgur.com/a/g9dMivi
Hi. I could be wrong but none of the tables in your image seems to be coming from an ERP system.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-02-2019, 05:15 PM   #13
Blancorn
Newly Registered User
 
Join Date: Feb 2016
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Blancorn is on a distinguished road
Re: Connecting Access Database with other software

You are right.
How can I add this ERP table to my database?
Blancorn is offline   Reply With Quote
Old 06-02-2019, 05:25 PM   #14
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,552
Thanks: 29
Thanked 631 Times in 614 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Connecting Access Database with other software

Quote:
Originally Posted by Blancorn View Post
You are right.
How can I add this ERP table to my database?
Hi. So, if you don稚 have a connection to the ERP tables yet, then the SQL statement they gave you won稚 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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-02-2019, 06:03 PM   #15
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,795
Thanks: 0
Thanked 433 Times in 429 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Connecting Access Database with other software

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/art...3-87AC8D1A2202

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 06-02-2019 at 06:12 PM.
June7 is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating stand alone software from access database? crododo General 3 05-03-2013 06:57 PM
Question Who owns Access database if part of software? fatfreddy General 1 06-21-2011 12:03 PM
Connecting with a back end access database dvdw Modules & VBA 4 04-15-2009 07:24 AM
Connecting Outlook's contacts to Access database ekta Forms 10 10-30-2003 08:33 AM
Connecting to another access database. peters Forms 1 06-27-2001 01:00 AM




All times are GMT -8. The time now is 12:57 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World