Access 2007 front-end for SQL Server 2008

[Kinger43]

I am attempting to create an Access 2007 front-end to an existing remote SQL Server 2008 database. I created a new Access Project (adp) and connected to an existing SQL database. I entered all information in the Data Link Properties dialog and clicked Test Connection. Access said it was successfull so I clicked OK on the Data Link Properties dialog expecting the database to connect. However, it does not. In the title bar at the top it says DATABASENAME (disconnected)roject (Access 2002-2003 file format). I checked permissions for the user (SQL Authentication) in Management Studio and I don't see any issues there as the user as db_owner permissions for the database. I can connect through Excel and link to all tables in the database using the same login. Is there something I'm missing?

 

[RichardA]

Hope you don't mind me hijacking this posting to find out if you ever cracked a problem you posted 2 years ago! I've just encountered it and can't seem to find a solution. You posted,

"I have written a batch file that calls shortcuts to 4 macros in 4 different Access databases. The goal is for this batch file to run every night and update the 4 databases. The problem that I am running into is that for one of the databases it will run the macro completely through and then throw me the error mentioned in the title where the [path/filename] is the shortcut to the macro that it just ran. Doesn't make sense that it will call the macro, open the database, run the macro, close the database (per a Quit statement at the end of the macro), and then say it can't find the shortcut that it just opened. Any help is appreciated."

Many thanks.

RichardA

 

[Kinger43]

I did solve this problem. I believe what was happening was that the batch file would essentially call each macro in order very quickly before they had a chance to run and there were multiple macros running at the same time possible using the same tables/queries. What I wound up having to do was add a pause between each line that calls a macro. Here is an example code:

start msaccess.exe "s:\AccessDB\AS400 Tables.mdb" /x "AS400 Database Update Macro"

ping 1.1.1.1 -n 1 -w 420000 >pause1

start msaccess.exe "c:\NISC\GIS16092.mdb" /x Update_GIS

This piece of code will call the macro "AS400 Database Update Macro" in the "AS400 Tables.mdb" database., pause for 420000ms=420s=7min (long macro, lots of queries, it took a while to complete), then run the "Update_GIS" macro in the "GIS16092.mdb" database. Essentially with the pause you just ping a non-existant IP address and tell it to do so for longer than it takes the macro before the pause to run (-w 4200000). The time must be specified in milliseconds. This keeps macros from running simultaneously. The >pause1 at the end is just the output file to dump to, there's nothing in it. This fixed my problem.

 

[RichardA]

Thanks for responding. My problem was lightly different in that it was happening while calling just one macro! But I've come up with a solution. The problem arose when I called the 'mdb' file directly on the command line (with the switch for the macro). If I call 'msaccess.exe' and add the 'mdb' as a parameter it all workes fine! So it may be something in the o/s or file associations somewhere. But thanks for your solution.

 

[Kinger43]

Still needing an answer to my original post. Anybody? Help would be much appreciated.

I am attempting to create an Access 2007 front-end to an existing remote SQL Server 2008 database. I created a new Access Project (adp) and connected to an existing SQL database. I entered all information in the Data Link Properties dialog and clicked Test Connection. Access said it was successfull so I clicked OK on the Data Link Properties dialog expecting the database to connect. However, it does not. In the title bar at the top it says DATABASENAME (disconnected)roject (Access 2002-2003 file format). I checked permissions for the user (SQL Authentication) in Management Studio and I don't see any issues there as the user as db_owner permissions for the database. I can connect through Excel and link to all tables in the database using the same login. Is there something I'm missing?

 

[tinyevil777]

Why don't you create a data source via ODBC? I imagine that would work and resolve any connectivity/authentication issues.

 

[Kinger43]

The ODBC connection has to exist anyway. I've jumped through all the authentication hoops. The test connection is coming back successful but it isn't connecting to the tables in the database.

 

[HiTechCoach]

Do you have the proper permissions to use the table in SQL Server?

 

[Kinger43]

New development. It appears to be a problems specific to my PC as I was able to access it the exact same way through a different PC on the network. Does anybody know of some setting that would disallow connecting to the database while at the same time make it look like it's allowing it? It may just be this jank PC!

 

[melands2012]

Hi there,

I'm involved in a project very similar; connecting access 2007 frontend to SQL server2008 database. I have succeeded in connecting to the database using both ODBC and ADODB. But i'M CONFUSED IN SELECTING WHICH ONE IS BEST TO GO FORWARD. Do you have any suggestions or could you explain why you choose ODBC connections?

thanks!

 

[HiTechCoach]

Hi there,

I'm involved in a project very similar; connecting access 2007 frontend to SQL server2008 database. I have succeeded in connecting to the database using both ODBC and ADODB. But i'M CONFUSED IN SELECTING WHICH ONE IS BEST TO GO FORWARD. Do you have any suggestions or could you explain why you choose ODBC connections?

thanks!

melands2012,

Please start a new thread.

 

[melands2012]

I have already started a new thread 'Connect to SQL server 2008 database across LAN from Acess 2007 Frontend ' . This thread started by Kinger was suggested to me in the 'Similar threads' list. I thought Kinger might be able to let me know some of his expeirience.
Anyway, Could you help me on this?
thanks