[Microsoft][ODBC Driver Manager] Data source name too long

[paulked]

Hi all

I'm getting

Run-time error '-2147467259 (80004005)':

when I try to open a connection.


Sub DoTrans()
Dim cn, dbPath, dbWb, scn, dsh, ssql

Set cn = CreateObject("ADODB.Connection")
dbPath = Application.ActiveWorkbook.Path & "/Epos1.accdb"
dbWb = Application.ActiveWorkbook.FullName
scn = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=" & dbPath
dsh = "[DB$]"

cn.Open scn '<<<--- Error here

ssql = "INSERT INTO fdClerk01 ([fdItem], [fdPrice], [fdDept], [fdSpare]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
cn.Execute ssql

End Sub

The DB is on Onedrive so the path and filename string is:

"https://d.docs.live.net/c43dcde2cf5b28ff/Fringe/Epos1.accdb"

I have a feeling that it could be the above path that is throwing it, but I need to keep the DB in the cloud so that more than one person can use it simultaneously.

Any ideas?

Thanks

Paul Ked

[SamT]

Moderator Bump

[Paul_Hossler]

Some things are VERY fussy about the parameter type(s) they are passed

I would try to

1. Use Option Explicit at the top of the module

2.Dim explicitly

3. Single step and use Immediate Window to verify that the strings you're building are what they should be

4. dbPath might have embedded spaces so you might need to handle them





Sub DoTrans()
Dim cn as Object, dbPath as String, dbWb as String, scn as String, dsh as String, ssql as String

Set cn = CreateObject("ADODB.Connection")
dbPath = Application.ActiveWorkbook.Path & "/Epos1.accdb"
dbWb = Application.ActiveWorkbook.FullName
scn = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=" & dbPath
dsh = "[DB$]"

cn.Open scn '<<<--- Error here

ssql = "INSERT INTO fdClerk01 ([fdItem], [fdPrice], [fdDept], [fdSpare]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
cn.Execute ssql

End Sub

[paulked]

Thanks for the bump :)

Thanks for info Paul, I
1. Always use OE
2. Tried that, no difference
3. Have done that, everything looks ok.
4. That comes through ok (I copied the address from the watch window in my original post)

Taking out the OLEDB; part of the scn string gives a "Method 'Open' of object'_Connection' failed"

I'll do some more digging.

Cheers

Paul Ked

[Paul_Hossler]

what is the value for scn after


scn = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=" & dbPath

from the immediate window?

[paulked]

21049
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=https://d.docs.live.net/c43dccf1cf5b28ff/Fringe/Epos1.accdb"

Thanks for looking

Paul Ked

[paulked]

I've made some progress...

I re-installed the Access DB Engine from https://www.microsoft.com/en-us/download/details.aspx?id=13255 then got rid of the OLEDB; part of scn.

That allowed me to go as far as trying to connect to the DB, BUT with an invalid file type.

Changing the file locations to Dropbox instead of Onedrive (so that it has \'s instead of /'s) now allows me to get as far as the actual execution... Then I got executed again!

Now getting "The INSERT INTO statement contains the following unknown field name: 'Item1'

I don't have 'Item1', I have 'Item', and changing the [fdItem] to [fdBalls] still gives me the same error ('Item1').



Sub DoTrans()
Dim cn As Object, dbPath As String, dbWb As String, scn As String, dsh As String, ssql As String
Set cn = CreateObject("ADODB.Connection")
dbPath = Application.ActiveWorkbook.Path & "\Epos1.accdb"
dbWb = Application.ActiveWorkbook.FullName
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
dsh = "[DB$]"
cn.Open scn
ssql = "INSERT INTO Clerk01 ([fdBalls], [fdPrice], [fdDept], [fdSpare]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
cn.Execute ssql

End Sub


Hell is a place on earth!

[paulked]

Got it!

Added a new sheet (rather than clearing the existing sheet) and hey presto!!!

Thanks guys for all your input.

Paul Ked :beerchug: