Run-Time Error 91 - VBA to Print to PDF (1 Viewer)

[btb9877]

Hey everyone,

Here is my situation. I have created a report in Access for around 800 uniquely identified line items. My end goal is to develop a script that will Print a PDF for each unique identifier and save them all in one folder and have each one named by unique identifier. Below is the script I have written, but for the life of me I cannot get through the Run-Time Error 91. The data is being derived from a SQL database. Any help is appreciated!

Private Sub Command35_Click()

Dim rst As ADODB.Recordset

Set rst = Currentdb.OpenRecordset("SELECT DISTINCT [Unique_Identifier] FROM [tbl_questionnaire] ORDER BY [Unique_identifier];", dbOpenSnapshot)

Do While Not rst.EOF
strRptFilter = "[Unique_identifier] = " & rst![UNIQUE_IDENTIFIER]

DoCmd.OutputTo acOutputReport, "eDeliverableReport", acFormatPDF, "C:\Users\Me\Desktop\Reports" & "\" & rst![UNIQUE_IDENTIFIER] & ".pdf"
DoEvents
rst.MoveNext
Loop

rst.Close
Set rst = Nothing

End Sub

 

[JHB]

In which line do you get the error?

 

[StarGrabber]

Hi btb9877, welcome to the forum.

I tried to run your code. Without success, as expected. But I got several other error numbers than 91. Error # 91 is thrown when an object variable (e.g. a recordset, a control, a class module, etc.) isn't initialized.

Your code cannot run because you are mixing ADO with DAO. "CurrentDb" is a DAO function. To access a "SQL database" an OLE DB (via ADO) or ODBC connection is required.

The following example shows how to connect using ADO to a Microsoft SQL Server database:

    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection

    cnn.Open "Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=[I]YourComputerName[/I]\SQLEXPRESS;" & _
        "Integrated Security=SSPI;Initial Catalog=[I]YourDatabaseName[/I];Data Provider=SQLOLEDB.1"

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    rst.Open "SELECT DISTINCT [Unique_Identifier] FROM [tbl_questionnaire] ORDER BY [Unique_identifier];", cnn

I guess "Option Explicit" is not set in your module header. You can avoid a lot of trouble with this option set. On the other hand you'll have to write more (but clean) code.

 

[boblarson]

Hi btb9877, welcome to the forum.

I tried to run your code. Without success, as expected. But I got several other error numbers than 91. Error # 91 is thrown when an object variable (e.g. a recordset, a control, a class module, etc.) isn't initialized.

Your code cannot run because you are mixing ADO with DAO. "CurrentDb" is a DAO function. To access a "SQL database" an OLE DB (via ADO) or ODBC connection is required.

The following example shows how to connect using ADO to a Microsoft SQL Server database:

    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
 
    cnn.Open "Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=[I]YourComputerName[/I]\SQLEXPRESS;" & _
        "Integrated Security=SSPI;Initial Catalog=[I]YourDatabaseName[/I];Data Provider=SQLOLEDB.1"
 
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
 
    rst.Open "SELECT DISTINCT [Unique_Identifier] FROM [tbl_questionnaire] ORDER BY [Unique_identifier];", cnn

I guess "Option Explicit" is not set in your module header. You can avoid a lot of trouble with this option set. On the other hand you'll have to write more (but clean) code.

You should NOT use a new connection for this. instead of

Set cnn As New AODB.Connection

it should be

Set cnn = CurrentProject.Connection

otherwise you can end up with errors.

 

[StarGrabber]

@boblarson:

With...

Set cnn As [B]New[/B] ADODB.Connection

..."you can end up with errors" if that connection corresponds to the one referenced by "CurrentProject.Connection" or "CurrentProject.AccessConnection". This one can be used for local or linked tables only.

What do you do, if there are none in the database frontend file (which concerning linked tables is much more flexible in regard to connection changes or re-establishments after frontend replacements due to updates)?

 

[boblarson]

@boblarson:

With...

Set cnn As [B]New[/B] ADODB.Connection

..."you can end up with errors" if that connection corresponds to the one referenced by "CurrentProject.Connection" or "CurrentProject.AccessConnection". This one can be used for local or linked tables only.

Yes, and that is what is normally done by people. But if you call out the same database you are in to create a new connection, you can actually quickly get the UNABLE TO CONNECT, TOO MANY CONNECTIONS error. There is no reason to create a new connection if the tables are internal or linked. If the table is in a completely different database, and isn't linked, then yes. Otherwise it is totally unneccessary and can cause problems as I've said.