Solved User-defined type not defined - Remote Db

[Ashfaque]

Hi,

My Access FE is at remote center and BE is on SQL Server.

For all local pcs the db working fine. But I have installed my FE on a remote db I mean in other city and need to get connected my Access FE from remote with DSN less connection code.

Nothing has been changed in code but on following line it is producing subjected error.

Dim db As DAO.Database

Do you think that any specific process require to run my remote db?

Please advise...my work has been stuck up due to this problem.

Any help shall be appreciated.

Regards,

 

[theDBguy]

Have you checked your References to see if any is missing?

 

[The_Doc_Man]

It depends on which version of Access you have installed, because some of the older versions didn't have DAO as a default "thing" to be defined. In the more modern versions of Access, DAO is part of the DB Engine library, but that wasn't always the case. So... which version of Access are we discussing?

 

[Ashfaque]

2016 version of Access at remote work station. This is the first time my Access FE is installed at remote pc.

For local (in same office) connection are made DSN less all the FE at each work station is working smoothly. But I think there should be some other code that will connected remotely to our office server.. I have no idea.

Need your expert advise......

 

[The_Doc_Man]

For the SQL back-end I must defer to my colleagues. I have not worked with SQL Server. My high-end DB was ORACLE-based. But regarding that remote connection - is it hard-wired or do you know if there is a WiFi component?

 

[Ashfaque]

Sir, We have Full Speed WiFi at our branch office. But no info about component in Access. Moreover, another ERP is installed at the same pc which is connecting to our server in our head office. So I also want my FE to connect to server tables but the code lines I am not gone thru or even I don't know if it is good idea to get connected remotely. The only thing which I could do is; only few relevant tables will be connected in FE at branch. This is to avoid traffic only.

Thanks in advance and waiting to overcome the problem....

 

[The_Doc_Man]

As I stated before, I have limited SQL server knowledge, but I do know that a claim for something to be undefined USUALLY resolves to a reference problem. You should get into the VBA code so that you can look at (menu bar) Tools >> References. See if there is a Database Engine object library. If THAT isn't checked, check it before you do anything else. Close the code view, close and save the DB, and try it.

If the DB Engine object WAS checked, I'm wondering if the problem is that the table on the other side of the implied link ISN'T a DAO object but rather is an ADO object. There is a Microsoft ActiveX Data Objects library which needs a reference check-mark in that case. But I didn't think it would be that picky. I will gladly defer to my colleagues on this one since I am limited in SQL server issues.

EDIT: After re-reading the description, the error points to a declaration, not an instantiation, so the FE doesn't know (yet) what it will have to connect. Therefore, with this class of error in a DIM statement, this HAS to be a library issue.

 

[Minty]

I'm in agreement - this will be a fresh Access install and I suspect the references aren't set correctly on the remote client.

 

[Ashfaque]

As I stated before, I have limited SQL server knowledge, but I do know that a claim for something to be undefined USUALLY resolves to a reference problem. You should get into the VBA code so that you can look at (menu bar) Tools >> References. See if there is a Database Engine object library. If THAT isn't checked, check it before you do anything else. Close the code view, close and save the DB, and try it.

If the DB Engine object WAS checked, I'm wondering if the problem is that the table on the other side of the implied link ISN'T a DAO object but rather is an ADO object. There is a Microsoft ActiveX Data Objects library which needs a reference check-mark in that case. But I didn't think it would be that picky. I will gladly defer to my colleagues on this one since I am limited in SQL server issues.

EDIT: After re-reading the description, the error points to a declaration, not an instantiation, so the FE doesn't know (yet) what it will have to connect. Therefore, with this class of error in a DIM statement, this HAS to be a library issue.

You are correct Sir,.

I installed Database Engine Object Lib (32Bit) on remote pc and then the error changed to attached one.

What step need to take to tackle this above attached error..

 

[Minty]

Have you checked you can connect on the SQL server on that machine? How are you authenticating?

 

[Ashfaque]

Have you checked you can connect on the SQL server on that machine? How are you authenticating?

Thanks Minty for pointing out this issue.

Yes, there was an issue of IP address. I placed the correct IP address and now it is connecting remotely to our head office sql server.

Thanks to The Doc Man also. His details on this issue supported me a lot.

Regards,

 

[The_Doc_Man]

We are here to help. Glad in this case that I could point the right way even though I know nothing much about SQL server.