code to build database

[Lino]

Hello,

Does anyone have the code to build a database:

I want to take my data in excel and move it to an access database using vba.

thanks,

Lino

 

[mdmilner]

Lino,

That's a pretty broad question.
Looks pretty similar to this thread:

http://www.mrexcel.com/board2/viewtopic.php?p=401812&highlight=#401812

Please take a look at this link:
http://www.mvps.org/access/general/gen0008.htm

Same website as the other.

Mike

 

[Lino]

thank you.

how about this one:

I have an excel spreadsheet with columns A through I and then columns L, O, R and Z that i want to export to an access database.

I am guessing I need to set variables equal to these cells and assign each variable to a field in the access database to get this to work.

I could not find this on mvps.org...help.

thanks,

Lino

 

[mdmilner]

This is the clip from that last link:
This would import from Excel into Access.

    DoCmd.TransferSpreadsheet transfertype:=acImport, _
            tablename:="tmpTableName", _
            FileName:="SomeExcelFile", Hasfieldnames:=True, _
            Range:="'WorkSheet Name'!", SpreadsheetType:=5
            
            'The Spreadsheet type = 5 specifies an Excel 5.0/7.0 file
            'format

This uses the built in Import function - same thing as if you went to the File-Get External Data-Import menu. The beauty of the method is, no, you do not need to pass fields into variables. Most of the time, Access will figure it out for you. It can even handle 'ignoring' columns in the middle that you don't want.

This is actually a function I wrote to handle my file import/exports/links

Public Function ImportExport(ByVal Ltype As String, ByVal Tname As String, _
                                 ByVal TLoc As String) As Long

On Error GoTo Err_handler
Debug.Print Ltype & " File " & TLoc
Select Case Ltype:
    Case "acImport":
      Ltype = 0
    Case "acExport":
      Ltype = 1
    Case "acLink":
      Ltype = 2
End Select

DoCmd.TransferSpreadsheet Ltype, 8, Tname, TLoc, True, ""
Exit Function

Err_handler:

 Select Case Err.Number:
  Case 2391:
    MsgBox Err.Number & " One or more fields in " & TLoc & " not in Database"
   Case 3051:
    ' Somebody is in the xls
  Case Else
    Debug.Print Err.Number & " " & Err.Description
 End Select
ImportExport = Err.Number
Err.Clear

End Function

Mike

 

[Lino]

I would appreciate it if you could explain your code.

I don't see how u are bringing in the data?

Thanks,

Lino

 

[Cbrine]

I would appreciate it if you could explain your code.

I don't see how u are bringing in the data?

Thanks,

Lino

The

Docmd.TransferSpreadsheet, acImport

is the command that transfers the data to your database
The acImport portion is stored in the variable Ltype, which is assigned based on the select case structure. The transferSpreadsheet can do a few different things, and his code allows for a passed variable that assigns the type of TransferSpreadsheet to be done.