Linking tables in GM using MS Access?

[sandingdude]

We wrote a MS Access database to create custom reports from the Goldmine 6.0 tables. We did this because it was easier to write the reports in Access than using the Goldmine Report writer. I am running into problems though:
-I need to link the tables from Goldmine to MS Access. When I do this I get an error in Access that says - 'could not lock table 'conthist' currently in use by user X on machines X.

Does anyone know a way around this? I want people to be able to stay in GM as well as run the reports on the fly. Thank you in advance.

 

[Hap007]

Dear sandingdude,

I have been very succesful in linking the GoldMine Tables from an Access database.

This sounds like what you are trying to do.

Make sure your access db is not opened 'Exclusive' mode.

Running an outside application from the GoldMine Toolbar will allow you to pass parameters to the external application from the current GoldMine record and still keep GoldMine active.

You might try the book 'Hacker's Guide to GoldMine' that has many excellent tips.

Explaining how to pass parameters is much more involved than the space this forum response window would allow. I could help answer specfic problems, but to explain the entire procedure is just too lengthy.

Another method would be to post your question on the Access Developer Forum here in Tek-Tips.

Good Luck,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[DataDivaJill]

All users should be out of GoldMine for you to establish the link. Then you should be ok after the link is established then people can get back into GM.

Instead of Access (dangerous) use Crystal report writer.
Works real good and you don't have the capability to write back into GM and destroy it.

 

[sandingdude]

Thanks for the tips guys. DataDiva they doen't want to purcahse anymore software.

Hap how do you put Access in Exclusive mode? And is there any way for me to prevent users from writing back data to access? I want one direction read only access.

Thanks,

Dave

 

[Hap007]

Hi Dave,

Access is a some what friendly environment, so if a user has the ability to open an access db, security is some what loose.
Yes, you can eliminate write back or change, but it can be difficult if your user is knowledgeable in access.

You could retrieve data in forms that have the data source set to read only or edit mode disabled. Several ways are possible, each one has it's good and bad features.

You could also create a read only query.

But, if the user is opening the tables, well then it is pretty tough to stop revisions once the table is opened.

Regarding 'Exclusive', the default mode is set in the 'Options' tab for access. To change at other times, depending on which version of access you are using, there is either a check box on the open dialog screen or a drop down button next to the 'Open' button in later versions of Access.

Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Solutus]

You really shouldn't ever try to link directly to a Goldmine .dbf table. It may work but it is not advisable. Instead, you want to set up an ODBC connection to the Goldmine tables. This is how many other reporting and add-on applications gather Goldmine data without affecting Goldmine.

Deborah Atkinson
GoldMine Consultant

http://www.solutuscrm.com

 

[sandingdude]

Hi Deborah

Thanks for your advice. Can you tell me how to create an ODBC connection for Goldmine?

Thank you in advance.

Dave

 

[Solutus]

I’m sorry, I was thinking Excel not Access. DataDivaJill is correct about Access. Access has its own built-in ODBC drivers. As long as you have everyone out of GoldMine and then link the tables, they should be able to get back in and you can still create your reports. Access only needs to lock the table to create the link.

If you want to access external data in Excel you need to create an ODBC source. Here are the steps:

1. In Windows XP, go to Start\Control Panel\Administration Tools and select Data Sources (ODBC). Or Start\Run and type odbcad32 and click OK.
2. Select the System DNS tab. We want to create a System Data Source for Goldmine, to do this click on the 'Add' button to display the 'Create New Data Source' screen.
3. Select the 'Microsoft dBase Driver (*.dbf) from the list and click 'Finish'.
4. Enter a 'Data Source Name' and 'Description' for example GoldMine_Contacts, uncheck the 'Use Current Directory' box and click on the 'Select Directory' button. In the resulting 'Select Directory' screen shown in figure 4, navigate to the 'Common' folder found in the main GoldMine installation directory (c:\Program Files\GoldMine by default), and choose OK.
5. The 'ODBC dBase Setup' screen should now appear as shown in Figure 5. This instruction is using GoldMine version 6, however for earlier versions of GoldMine it may be necessary to select 'dBase III' or 'dBase IV' from the 'Version' dropdown.
6. To complete the creation of the ODBC connection, select OK on the ' ODBC dBase Setup' screen and close the 'ODBC Data Source Administrator' application.
If you are running an older copy of Windows or Word, you may need to install the new Microsoft DAC. To install the ODBC drivers perform the following steps:
Note: Users of Microsoft Office Small Business Edition may find the ODBC setup in the "Value Pack" under "Data Access." Or download the Microsoft Data Access Components (DAC) from Microsoft's website.
You do not need to select (or unselect) any other options when installing the DAC. Just run setup and follow the default prompts.
Then, in Excel you can go to Data\Import External Data and choose New Database Query. If you follow the wizard from there, it should connect to the tables via ODBC and bring up the data that you require.




-----------------
Deborah
GoldMine Consultant - Seattle

http://www.SolutusCRM.com

 

[sandingdude]

I want to thank everyone for their help. I will go ahead and try everything you suggested.

Deborah - I'm having trouble with Access seeing the Goldmine Date as a Date. So when I run a filter I get records outside of my date range.

I have that post titled: Date Filter in Query not working??
thread701-1284815

Any help would be suggested?

 

[sandingdude]

Hi Deborah

I hope you had a nice holiday. I started playing around with your instructions which worked very well. I was able to connect to the GM database, but I have a problem. It only brings 1 record into my spreadsheet.

Any ideas?

Thanks,

Dave