Need VBA code to set ADODB to Current DB

[DougP]

Conn.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = \\Compaqserver\apps\Databases\Sched2000.mdb"

I get an error on the first line of code above because I am in the database.

Error is: “The Database has been put in a state by user ‘Admin’ on machine MIS2 that prevent it from opened or Locked”
I’m Admin on MIS2

In the old way you could set the Database = to current db like so:

Set db = Currentdb
Does anyone know the syntax for setting the ADODB connection that way???
I saw it somewhere but can't find it again.

TIA
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[TTThio]

This is what I find in the book:

Set CurDB = CurrentDB
Set Conn = New ADODB.Connection

With Conn
.provider = "...."
.connectionstring = "data source= " & CurDB.Name
.open
End With

Good Luck!

 

[DougP]

OK I got it sorry for the wild goose chase
I forgot to add the second line of the code
here is the whole thing
-----------------------
' Open SQL Server 7 Table
Dim Conn As ADODB.Connection, MySQL, SQLCode As String
Set Conn = New ADODB.Connection

Set MySQL = New ADODB.Recordset
Conn.Open "driver=SQL Server;server=smallbserver;uid=sa;pwd=;database=Universal;"
SQLCode = "INSERT INTO ScheduleIT (JobNumber, CreateDate, NeededBY, DwgNo, ReturnToDept, Path) VALUES ('" & Me!WIPNumber & "', '" & Me!StartDate & "', '" & Me!EndDate & "', '" & Me!DWGNumber & "', 'INV', '" & JustPath & "')"
MySQL.Open SQLCode, Conn, adOpenStatic, adLockOptimistic ' <<<<< Changed adLockOptimistic to AdReadOnly

Set MySQL = Nothing
Set Conn = Nothing
------------------

when I changed adLockOptimistic to AdReadOnly it works perfect.

Thanks ALL ;-) DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[vbajock]

This is a function I use.

Function OpenReportsDatabase(ByVal strDbname As String)

On Error GoTo OpenReportsDatabase_err

Set conReportdb = Nothing

If strDbname = &quot;&quot; Then 'if DSN string is empty
'use current db
Set conReportdb = CurrentProject.Connection
Else
conReportdb.Open_(&quot;Provider=Microsoft.jet.OLEDB.4.0;Data Source=&quot; +strDbname)

End If

OpenReportsDatabase = True
Exit Function



'***********************
OpenReportsDatabase_err:
'***********************

MsgBox Str$(Err) + Error$

OpenReportsDatabase = False




End Function