aneh... bener2 aneh...
tu function udah u coba perbaikin ya? connection stringnya udah mengarah ke
SQL Server koq... wakakakaka... malah parameter input "DBAccess as String"
di function-header ngga kepake blass...
klo connect ke access, mustinya connectionstringnya jadi kya something like
this :
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& DBAccess & _
";Persist Security Info=False"
n klo mo connect ke SQL server, mending function headernya u ganti aja deh
misal :
Public Function Export2XL(InitRow As Long, DBServer As String, DBUser as
string, DBPassword as string, DBSchema as string, DBTable As String) As Long
...biar masih memfasilitasi konsep 'reusable' dari sebuah routines.
trus connection-stringnya u sesuaikan jadi kya gini :
cn.connectionstring = "Provider=SQLOLEDB;" _
& "Data Source = " & DBServer & ";" _
& "Initial Catalog = " & DBSchema & ";" _
& "User ID = " & DBUser & ";" _
& "Password = " & DBPassword & ";"
~~
jadi, klo mo manggil function nya,
Export2XL 3, "serv_bandung", "aconx", "barakuda", "Northwind", "Publisher"
diasumsikan, kmu connect ke SQL Server di "serv_bandung", make otoritasnya
user "aconx" yg punya password "barakuda" dengan database yg mo dipake
adalah "Northwind" n tabel yg mo di-export ke excel namanya "Publisher"
hehehe... aku masih merasa aneh contoh mu tu bisa kamu jalanin beneran apa
ngga. soalnya ngga ada perintah yang memfasilitasi mbuka koneksi ke file
access BIBLIO.MDB blass di codingmu :D
peace love n respect,
Rizky Prihanto
~~~~~~~~~~~~~~~~~~~
Office : C4 Computer Communities Club(http://www.cee-papat.com)
Personal : http://www.software-arsitek.web.id
On 11/4/07, Junior <[EMAIL PROTECTED]> wrote:
>
> Doni Rahadian wrote:
> > saya punya contoh perintah untuk mengimport data di tabel access ke
> excel :
> >
> > Export2XL 3, ".\BIBLIO.MDB", "PUBLISHERS"
> >
> > untuk koding di modulenya :
> >
> > Public Function Export2XL(InitRow As Long, DBAccess As String, DBTable
> As
> > String) As Long
> >
> > Dim cn As New ADODB.Connection 'Use for the connection string
> > Dim cmd As New ADODB.Command 'Use for the command for the DB
> > Dim rs As New ADODB.Recordset 'Recordset return from the DB
> > Dim MyIndex As Integer 'Used for Index
> > Dim MyRecordCount As Long 'Store the number of record
> on
> > the table
> > Dim MyFieldCount As Integer 'Store the number of
> fields
> > or column
> > Dim ApExcel As Object 'To open Excel
> > Dim MyCol As String
> > Dim Response As Integer
> >
> > Set ApExcel = CreateObject("Excel.application") 'Creates an object
> >
> > ApExcel.Visible = True 'This
> enable
> > you to see the process in Excel
> > ApExcel.Workbooks.Add 'Adds a new
> book.
> >
> > 'Set the connection string
> > cn.ConnectionString = "Provider=sqloledb.1;" & _
> > "Password=barakuda;" & _
> > "user id = aconx;" & _
> > "initial catalog=merchant;" & _
> > "data source = svr_bandung"
> >
> > 'Open the connection
> > cn.Open
> >
> > 'Check that the connection is open
> > If cn.State = 0 Then cn.Open
> > Set cmd.ActiveConnection = cn
> > cmd.CommandText = DBTable
> > cmd.CommandType = adCmdTable
> > Set rs = cmd.Execute
> > 'Count the number of fields or column
> > MyFieldCount = rs.Fields.Count
> >
> > 'Fill the first line with the name of the fields
> > For MyIndex = 0 To MyFieldCount - 1
> > ApExcel.Cells(InitRow, (MyIndex + 1)).Formula = rs.Fields
> (MyIndex).Name
> > 'Write Title to a Cell
> > ApExcel.Cells(InitRow, (MyIndex + 1)).Font.Bold = True
> > ApExcel.Cells(InitRow, (MyIndex + 1)).interior.colorindex = 36
> > ApExcel.Cells(InitRow, (MyIndex + 1)).WrapText = True
> > Next
> >
> > 'Draw border on the title line
> > MyCol = Chr((64 + MyIndex)) & InitRow
> > ApExcel.Range("A" & InitRow & ":" & MyCol).Borders.Color = RGB(0, 0, 0)
> > MyRecordCount = 1 + InitRow
> >
> > 'Fill the excel book with the values from the database
> > Do While rs.EOF = False
> > For MyIndex = 1 To MyFieldCount
> > ApExcel.Cells(MyRecordCount, MyIndex).Formula = rs((MyIndex -
> > 1)).Value 'Write Value to a Cell
> > ApExcel.Cells(MyRecordCount, MyIndex).WrapText = False 'Format the
> Cell
> > Next
> > MyRecordCount = MyRecordCount + 1
> > rs.MoveNext
> > If MyRecordCount > 50 Then
> > Exit Do
> > End If
> > Loop
> >
> > 'Suggest to the user to save it's work
> > Response = MsgBox("Save the Excel Sheet and clik OK", vbOKOnly, "Save
> your
> > file")
> >
> > 'Close the connection with the DB
> > rs.Close
> >
> > 'Return the last position in the workbook
> > Export2XL = MyRecordCount
> >
> > End Function
> >
> > HASILNYA BERJALAN, yang mau saya tanyakan bagaimana koding untuk
> database
> > menggunakan sql server 2000
> >
> > MOHON SEKALI PENCERAHANNYA ...thanks all vb-ers
> >
>
>
[Non-text portions of this message have been removed]
