Run-Time Error '13': Type Mismatch

[darkhat01]

I am getting a Run-Time Error '13': Type Mismatch. The Set db_data = NewConnection is what is highlighted in yellow. Would anyone know why this is happening? I got the code from:

http://www.tek-tips.com/faqs.cfm?fid=5250

I am trying to learn how to import a csv file into a access table. Also is the DSN the location of where the csv files should be???

Private Sub ImportData_Click()


    MsgBox "The ID being used is: " & intID 'Error Check
    MsgBox "Using dbFile: " & dbFile 'Error Check
    
    Set db_data = NewConnection
    db_data.CursorLocation = adUseClient

    'Set up DSNless connection
    db_data.Open "PROVIDER=MSDASQL;dsn=C:\MyDB Files;uid=;pwd=;database=;"

    Set Ado_data = NewRecordset
    
    'Note exactly the same select statement as would be used in a relational database

    Ado_data.Open "select name, salary from employee.csv e, salary.csv s " & _
    "where e.id=s.id", adOpenStatic, adLockOptimistic

    If Ado_data.RecordCount < 1 Then
        MsgBox "No data found"
        Exit Sub
    End If

    'Go to the first record in the set and loop around till no more available

    Ado_data.MoveFirst
    For i = 0 To Ado_data.RecordCount - 1
        MsgBox (Ado_data.Fields.Item(0).Value & " " & Ado_data.Fields.Item(1).Value)
        Ado_data.MoveNext
    Next

    Ado_data.Close
    db_data.Close


End Sub

Thanks,

Darkhat01

 

[xlbo]

You need to DIM your variable as the appropriate object eg. DIM db_Data as ADODB.Connection

To get the appropriate options there, you will need to make sure that you have the appropriate references set - that will depend on the type of connection you are using and the version of office.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[taupirho]

The DSN is the name you gave to the text driver you should have set up - its not the name of a file or directory.

If you don't specify the path to the text files in your select statement it will assume they are in the directory from which you run the program.

Aslo, importantly, you have not copied down the code in the FAQ correctly

It should be

Set db_data = New Connection NOT
Set db_data = NewConnection

 

[darkhat01]

I made the changes that both of you suggested. But I am getting an error that still highlights New Connection. I also Made the DIM change.

Why would this be....

Maybe I should use a FileSystemObject... Any ideas on this? Does anyone know of a tutorial for using FileSystemObject and doing a text stream out on the web?

 

[xlbo]

please post what you have changed your code to

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

using FileSystemObject

http://msdn.microsoft.com/library/en-us/script56/html/af4423b2-4ee8-41d6-a704-49926cd4d2e8.asp

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[darkhat01]

Sorry the new code is:

Private Sub ImportData_Click() 
Dim intID As Integer 'ID from manager record
Dim dbFile As String 'Location of our Access database file
Dim sSQL As String 'Our SQL query
Dim db_Data As ADODB.Connection

dbFile = Me!txtFileLocation
intID = Me!ID

 MsgBox "The ID being used is: " & intID 'Error Check
 MsgBox "Using dbFile: " & dbFile 'Error Check
    
    Set db_Data = New Connection
    db_Data.CursorLocation = adUseClient

    'Set up DSNless connection
    db_Data.Open "PROVIDER=MSDASQL;dsn=C:\Documents and Settings\treptor\Desktop\RaysDB;uid=;pwd=;database=;"

    Set Ado_data = NewRecordset
    
    'Note exactly the same select statement as would be used in a relational database

    Ado_data.Open "select name, salary from employee.csv e, salary.csv s " & _
    "where e.id=s.id", adOpenStatic, adLockOptimistic

    If Ado_data.RecordCount < 1 Then
        MsgBox "No data found"
        Exit Sub
    End If

    'Go to the first record in the set and loop around till no more available

    Ado_data.MoveFirst
    For i = 0 To Ado_data.RecordCount - 1
        MsgBox (Ado_data.Fields.Item(0).Value & " " & Ado_data.Fields.Item(1).Value)
        Ado_data.MoveNext
    Next

    Ado_data.Close
    db_Data.Close

End Sub

Thanks again for your help in trying to understand this,

darkhat

 

[xlbo]

Set db_Data = New ADODB.Connection

You must qualify all your references properly

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JerryKlmns]

Dim intID As Integer 'ID from manager record
Dim dbFile As String 'Location of our Access database file
Dim db_Data As ADODB.Connection
Dim Ado_data As ADODB.Recordset
Dim sSQL As String 'Our SQL query

dbFile = Me!txtFileLocation
intID = Me!ID

MsgBox "The ID being used is: " & intID 'Error Check
MsgBox "Using dbFile: " & dbFile 'Error Check

'Connection    
Set db_Data = New ADODB.Connection
db_Data.CursorLocation = adUseClient
db_Data.Open "PROVIDER=MSDASQL;dsn=C:\Documents and Settings\treptor\Desktop\RaysDB;uid=;pwd=;database=;"

'Recordset
sSQL = "SELECT name, salary " & _
       "FROM employee.csv As e, salary.csv As S " & _
       "WHERE e.id=s.id;"

Set Ado_data = New ADODB.Recordset
With Ado_data
   .CursorLocation = adUseClient
   .CursorType = adOpenStatic
   .LockType = adLockReadOnly 
   .Source = sSQL 
   .Open
   If Not (.BOF And .EOF) Then
      MsgBox "No data found"
   Else
      Do While Not .EOF
         MsgBox .Fields(0) & " " & Fields(1)
         .MoveNext
      Loop
   End If
   .Close
End With
Set Ado_data = Nothing
db_Data.Close
Set db_Data = Nothing

End Sub

A very nice recent thread222-1363106 to study, btw on cursors, locks etc.
Example is (very) analytical for demostration reasons ...

 

[darkhat01]

Geoff,

Can you explain to me how the ADODB.Connection works. I really don't understand it.

What is the difference between: New ADODB.Connection
and CreatObject("ADODB.Connection") don't they both create new objects?

thanks,

Darkhat

 

[xlbo]

That is not your issue - your issue is that you didn't qualify the "Connection" as an ADODB.Connection. You can have lots of different types of connections, therefore you must explicitly tell VBA what kind of connection it is.

How to explain what an ADODB connection is....well....the clue is in the name - it is an object that sets a reference to a connection to a database

To get data from a database, you generally need 4 things

1: The database object
2: The connection object (this should include any permissions)
3: A valid SQL string
4: a recordset object to hold the results of the SQL execution

All the objects need to be explicitly defined

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[darkhat01]

One more question, say I did a

Set objConnection = CreatObject("ADODB.Connection")

If I wanted to open a database in an other location to store it in objConnection I would do a:

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = c:\test.mdb"

But how would I open a table in the current database that I am in? Say I have a Table called Employees????

thanks,

darkhat

 

[xlbo]

If it is in the current database, you do not need a connection but how you reference the table will depend on what you are trying to do with it...

If you want to query a table in your current database, I think you can just use the database object to run the SQL.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[darkhat01]

Thanks....

 

[JerryKlmns]

darkhat01


If you still want an ADODB.Connection for the currently open db then

Dim Cnn As ADODB.Connection
Set Cnn = CurrentProject.Connection

 

[darkhat01]

Thanks Jerry...