Is it possible to link fields in Access to project? 2

[Teejay35]

I have an Access Database which I use for planning Production. However, I would like to produce a Gannt chart from this information. The only way I can think of doing this is by linking it to project, but I'm not sure how. Can anyone help me please?

 

[cmmrfrds]

Read up on OpenQuery in SQL Server BOL. Estentially, the OpenQuery allows you to read another databse from sql server. So, you pass the SQL to sql server which then reads the other database and passes the resultset back to you. In your case, an Access MDB.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5xix.asp

 

[lameid]

Thanks cmmrfrds! I had no idea it was possible.

Noteworthy however is that I had to explicitly grant the admin user permission to the object when logging in as admin using OpenRowset. Otherwise I got an error stating that the table did not have any columns.

 

[lameid]

I posted too soon... OpenRowset won't run from within access even if I create the procedure in SQL Query analyzer and make sure it works.

And on the flipside, I get an error when trying to use openquery...

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].


For grins I placed a default system.mdw in the server's windows\system32 folder.

Does Access need to be installed on the server?

Slightly edited link creation below that I ran before the openquery...

GO
EXEC sp_addlinkedserver
@server = 'Pricing',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = '<Path and Filename>.mdb'
GO

Any ideas?

 

[cmmrfrds]

When you ran OpenQuery was the MDB you were accessing the one you ran the OpenQuery from. If so, it is open exculsive since you are in design mode. Access should not need to be installed on the Server, but in my environment it is so I can't test that scenario.

If you setup your Access table as a "linked server" then the table can be treated like any other sql server table and there is no need to use OpenQuery. OpenQuery is for the cases where you do not want to setup a "linked server".

 

[cmmrfrds]

Here is some test code I ran on my PC. It has both sql server and access installed, but in theory the
C: can be replace with the UNC name of the PC.
\\yourPC\aemptydir\employee2.mdb'; in this example.


Public Function rowsetInsert()
'-- set reference to ADO library
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cn As New ADODB.Connection, sqlstring As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=localhost;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sqlstring = "insert Into "
sqlstring = sqlstring & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
sqlstring = sqlstring & "'C:\aemptydir\employee2.mdb';'admin';, NewShippers) "
sqlstring = sqlstring & "(CompanyName, Phone) "
sqlstring = sqlstring & "Select CompanyName, Phone From Shippers"

rs.Open sqlstring, cn, adOpenForwardOnly, adLockReadOnly

End Function

 

[lameid]

cmmrfrds,

Per your first post, I am attempting to connect to a Jet Database from within SQL server and in my case join it to an SQL server view for use in an ADP.

According to BOL OpenQuery requires a link server and by looking through See Also topics, I see that sp_addlinkedserver is how you add it.

I've done so. My error is specific. Searching the MSKB displays an article with a broken link. From what I can gather it has the solution. Apparently there is something specific to do with having Jet security on the database from what I can gather about using the OLE DB provider (my post about explicitly adding admin permissions to the table supports this). The problem with me using a recordset is that the 'query' is the recordsource for a form. If I can't get it to work with SQL Server, I'll go with my original solution and have an MDB front end for the 2 impacted reports and everything else in the ADP.

 

[lameid]

I got it to work on my MSDE... Which tells me it has to be either permissions to the SQL Service account temporary folders or something to do with the connection or the fact that Access is not installed (a workgroup is not bound properly to the OLE DB provider).

I read MSKB articles that said that both the Temp and TMP environment variables had to have permission for the sql service account and any users (if using windows authentication) that run the statement. Once all this is set the SQL server services can be restarted. My SQL server is production so I am stuck until later. Besides I think the SA account has the same file permissions as the service account and my existing permissions for that scenario are good. I just wish that the relevent KB article hadn't disappeared.

 

[cmmrfrds]

Were you able to setup the linked server in the production environment?

Can you run against it using Query Analyzer or Enterprise Manager?

Have you checked that the OLEDB to Access works on that PC.
There is an example how to test the OLEDB in this thread.
Thread705-1245040

 

[lameid]

See thread183-1251632

I successfully created a view in my ADP to pull records from the Jet Table once I had the link working. Weird scenario though... Enterprise manager uI works and stored procedure does not.