CurrentDB not valid in SQL SERVER

[patrickd123]

Hi,

Since I upsized my Access database to SQL server. One vital statement is not working anymore:

Private Sub Form_Load()
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM TimeDot where ID = " & EmployeeID)
End Sub

The statement: Set rs = db.Open.........
returns following error: "Run-time error 91 -> Object variable or with block variable not set"

In fact, in the debugger I see that the value of db after the affectation from CurrentDB is "Nothing". Like if the CurrentDB is no more valid in sql server. Does anybody know which global variable I must now use?

Any help will be appreciated

Thanks

 

[pbaldy]

I use SQL Server, and it works fine. One difference is that I always disambiguate the declaration:

Dim db As DAO.Database

Same for the recordset. I don't see you declaring either one.

 

[RoyVidar]

It sounds like you're working with an ADP, not a mdb, in which case it doesn't seem like CurrentdDB is a working part of the application object.

I think you need to either convert to linked mdb file, or start using ADO.

 

[patrickd123]

It sounds like you're working with an ADP........I think you need to either convert to linked mdb file, or start using ADO.

Yes, I am working with an ADP indeed. Can somebody give me some clues on the best way to scale back my project to a linked MDB, knowing that I have lots of data already in my tables That I would like to keep(if possible).

Thank you.

 

[RoyVidar]

You don't need to "scale back", ADP is one way of working with Access as front end to SQL server, but you can also use a mdb file, and link to the server, which is what I'd suggest.

In a copy of the original mdb file (the one you uppsized), remove any links to tables (the ones which were linked to your Access/Jet backend). If you used local tables in stead of linked, remove them.

Then link to the SQL server database (File | Get External Data | Link Tables...)

The linked tables, will probably be prefixed "dbo_", which you can remove, and hopefully, the app should work as before.

 

[patrickd123]

But isn't this for a local sql server database? Because the link tables menu ask for file emplacement: my is on a remote web server......

I had choose to do the ADP way because the wizard was asking me for all server info and managed somehow to connect me and it works.

So after your advices I'm facing 2 options:

1-Stay ADP a find a way to connect and use ADO. I think this easiest from where I'm at. Currently:
My application is 80% completed. I was able to do all my forms and even to do some coding because it looks like connection is not needed to do some database insert and delete(using DoCmd.RunSQL). My only problem now is when I need to write select statements because I needed a recordset which needs a connection and I have a hard time finding the all connections parameters for my sql server connection

2-Go back to my MDB and upsize. Problem is I did a lot of work since the upsizing and starting from before the upsizing would be quite a drawback.

So unless somebody has a solutioon for my second option, I think I will have to go with option 1.

 

[patrickd123]

Thanks to my lucky star, I finally was able to build my cooection string with the required parameters. Will go with options 1.

And thanks to you!!!!

 

[RoyVidar]

When using the current database (mdb and ADP), you can use

currentproject.connection

dim cn as adodb.connection
dim rs as adodb.recordset
set cn = currentproject.connection
' readonly/forwardonly
Set rs = cn.execute("Select * FROM TimeDot where ID = " & EmployeeID, adcmdtext)

' or perhaps keyset
' set rs = new adodb.recordset
' with rs
'     set .activeconnection = cn
'     .cursortype = adopenkeyset
'     .locktype = adlockoptimistic
'     .open "Select * FROM TimeDot where ID = " & EmployeeID,,,,adcmdtext
' end with

typed not tested

 

[patrickd123]

Tried it.
Loved it.

You're right again!!!!

Thanks

 

[niti]

Were you able to fix it using mdb to connect to sql server without changing it to ADODB.

CurrentDB() to connect to Sql server without linked tables