Connections fail on some files, not others

**c00ly81** · 05-12-2016, 07:31 AM

Hi
I recorded a macro that should connect a table in my workbook to different Access databases. I changed some of the code so that it finds the filepath in a cell in one of the worksheets. The code works fine on meny different Access databases. But not on others. I can't figure out why...

    Range("Tabell_Database_1[[#Headers],[ID]]").Select

    With ActiveWorkbook.Connections(1).OLEDBConnection

        .BackgroundQuery = True

        .CommandText = Array("Resultater")

        .CommandType = xlCmdTable

        .Connection = Array( _

        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Worksheets("Samlet").Range("O1") & ";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLE" _

        , _

        "DB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Pa" _

        , _

        "rtial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;" _

        , _

        "Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet" _

        , _

        " OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False" _

        )

        .RefreshOnFileOpen = False

        .SavePassword = False

        .SourceConnectionFile = ""

        .SourceDataFile = Worksheets("Samlet").Range("O1")

        .ServerCredentialsMethod = xlCredentialsMethodIntegrated

        .AlwaysUseConnectionFile = False

 

 

 

        End With

    With ActiveWorkbook.Connections(1)

        .Name = Worksheets("Samlet").Range("O2").Text

        .Description = ""

    End With

    ActiveWorkbook.Connections(1).Refresh

**dflak** · 05-12-2016, 05:27 PM

The best thing to do with a connection string is to get the string, break it into pieces, change the parts you want and put them back together.

Sub Change_Connection()
Dim ConnString As String
Dim ConnPieces() As String

' Get the connection string
ConnString = ActiveWorkbook.Connections("qry_Model_D").ODBCConnection.Connection

' This is what the connection string looks like
' DSN=RPTPRD01;UID=myname;PWD=XXX;DBQ=RPTPRD01;DBA=W;APA=T;EXC=F;FEN=T;
' QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;
' CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;

MsgBox ConnString ' So you can see it

 ' Split the connection string into pieces
ConnPieces = Split(ConnString, ";")

' Change the parts you want
ConnPieces(1) = "UID=NewName"   ' User Name
ConnPieces(2) = "PWD=YYY"       ' Password

' Put it back together
ConnString = Join(ConnPieces, ";")

End Sub

Connections fail on some files, not others

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Connections fail on some files, not others

Re: Connections fail on some files, not others

Thread Information

Users Browsing this Thread

Similar Threads

How to make a cell display "FAIL" when range of cells is FAIL

[SOLVED] deleting names associated with connections to CSV files

deleting connections to CSV files

[SOLVED] Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is N

Data connections have been disabled message - but no data connections?

SQL connections

[SOLVED] remote connections

Bookmarks

Bookmarks

Posting Permissions