Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Connecting to DBF files from VB (Read 2497 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Connecting to DBF files from VB
24.02.10 at 12:18:15
 
Connecting to DBF files from VB
 
Quote:
If I connect via SQL-XL Sql dialog Windows. I can connect the dbf files. But When I want to connect with VB, I can’t. What can I do ?

 
I have tried this and cannot find any problem with it. I recorded a macro while connecting to the DBF files and running a simpel query.
 
Perhaps you can check the code I generated and see where your code differs from mine. It should be as simpel as recording a macro to create the database connection. Also in VB.
 
Edited:
Note that in SQL*XL 5.2 the syntax changed slightly. The ConnectionType parameter became read only and instead the ConnectionType is passed as a parameter to the Connect routine. For further information see:
http://www.oraxcel.com/projects/sqlxl/help/vba/SQLXL/Database/index.html

 
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 24/02/2010 by gjlinker
'

'
    SQLXL.InitialiseSQLXL
    SQLXL.Database.ConnectionType = litSQLXLADO
    SQLXL.Database.Connect UserName:="", PassWord:="", DBAlias:="dbf_test (\\server\share\dbf_test)", ConnectionString:="Provider=MSDASQL.1;Extended Properties=""DBQ=\\server\share\dbf_test;DefaultDir=\\server\share;Driver={Microsoft dBASE Driver (*.dbf)};DriverId=277;MaxBufferSize=2048;PageTimeout=600;""", AllowTransactions:=True
    Range("A2").Select
    SQLXL.Sql.setText "select count(*) from a"
    Set SQLXL.Sql.Statements(1).Target = SQLXL.Targets(litExcel)
    SQLXL.Sql.Statements(1).OptimiseForLargeQuery = False
    With SQLXL.Targets(litExcel)
	  .AutoFilter = False
	  .AutoFit = True
	  .Headings = True
	  .Sort = False
	  .StartFromCell = "$A$2"
	  .Transpose = False
	  .SQLInNote = True
	  .ShowNote = True
	  .FormatData = True
	  .FreezePanes = False
	  .PasteInsert = False
    End With
    With SQLXL.Sql.Statements(1)
	  .ShowParametersDlg = False
	  .ShowResultsetDlg = False
    End With
    SQLXL.Sql.Statements(1).Execute
End Sub
 

Back to top
 
« Last Edit: 30.07.14 at 13:50:59 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1