常連

注目の的

社会貢献

質問力

### accessで256列以上のデータをCSVにエクスポートしたい

**困っていること：** あるシステムにデータを取り込むためのCSVファイルを作成しようとしています。そのシステムの取り込みファイルは276列ありますが、accessではテーブルに255列までしか作成できないため、データ取込み用CSVファイルが作成できません。

**やったこと：** とりあえず作成できる列まで1つのテーブルで作成し、残りの列はVBAでCSVに直接書き込みに行くような形でヘッダだけは276列まで作ることができました。
検索していろんな方のコードを参考に作ったものなので、つぎはぎ感がすごいですが、何とかできました。
付け足した20列ほどは全て空のセルなので、何も値は入らないですが、入力データフォーマットが取り込む行すべてに指定の276列ないと取り込めないため、ヘッダだけ作成してもでは取り込むことができません。（苦労してヘッダだけ作った意味はなかったみたいです。）

作ったものは以下です。やりたいことがご理解いただきやすいかと思い念のために掲載しておきます。
お知恵を拝借できますと幸いです。どうぞよろしくお願い致します。

```ここに言語を入力
Private Sub ファイル出力_Click()
  
   DoCmd.SetWarnings False
  
  DoCmd.OpenQuery "T_TUGデータ追加"
    
  
  DoCmd.TransferText _
    TransferType:=acExportDelim, _
    TableName:="T_TUGXray", _
    filename:="V:\フォルダ\TUG" & ".csv", _
    HasFieldNames:=True
    
      
' Private Sub ExTextWrite()
'    Dim fno As Integer
'    fno = FreeFile
'    TableName = "T_TUGXray"
'    Open "V:\フォルダ\TUG.csv" For Append As fno
'    Print #fno, TableName
'    Close fno
'End Sub


Dim xlsApp As Object
Dim xlsbook As Object
Dim xlsSheet As Object

Dim filename As String

 

filename = "V:\フォルダ\TUG.csv"

Set xlsApp = CreateObject("Excel.Application")

xlsApp.DisplayAlerts = False 

Set xlsbook = xlsApp.Workbooks.Open(filename)

Set xlsSheet = xlsbook.worksheets(1)


With xlsSheet

'行を削除
.Rows(1).Delete

End With

'保存
xlsbook.Save

xlsApp.Application.Quit

Set xlsSheet = Nothing
Set xlsbook = Nothing
Set xlsApp = Nothing


'ヘッダ挿入
    
Const ForReading = 1
Const ForWriting = 2
Dim objFSO As Object
Dim objFile As Object
Dim strContents As String
Dim strFirstLine As String
Dim strNewContents As String
Dim strPath As String

strPath = "V:\フォルダ\TUG.csv"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strPath, ForReading)
 
strContents = objFile.ReadAll
objFile.Close
 
strFirstLine = "1" & "," & "2" & "," & "3" & "," & "4" & "," & "5" & "," & "6" & "," & "7" & "," & "8" & "," & "9" & "," & "10" & "," & "11" & "," & "12" & "," & "13" & "," & "14" & "," & "15" & "," & "16" & "," & "17" & "," & "18" & "," & "19" & "," & "20" & "," & "21" & "," & "22" & "," & "23" & "," & "24" & "," & "25" & "," & "26" & "," & "27" & "," & "28" & "," & "29" & "," & "30" & "," & "31" & "," & "32" & "," & "33" & "," & "34" & "," & "35" & "," & "36" & "," & "37" & "," & "38" & "," & "39" & "," & "40" & "," & "41" & "," & "42" & "," & "43" & "," & "44" & "," & "45" & "," & "46" & "," & "47" & "," & "48" & "," & "49" & "," & "50" & "," & "51" & "," & "52" & "," & "53" & "," & "54" & "," & "55" & "," & "56" & "," & "57" & "," & "58" & "," & "59" & "," & "60" & "," & "61" & "," & "62" & "," & "63" & "," & "64" & "," & "65" & "," & "66" & "," & "67" & "," & "68" & "," & "69" & "," & "70" & "," & "71" & "," & "72" & "," & "73" & "," & "74" & "," & "75" & "," & "76" & "," & "77" & "," & "78" & "," & "79" _
 & "," & "80" & "," & "81" & "," & "82" & "," & "83" & "," & "84" & "," & "85" & "," & "86" & "," & "87" & "," & "88" & "," & "89" & "," & "90" & "," & "91" & "," & "92" & "," & "93" & "," & "94" & "," & "95" & "," & "96" & "," & "97" & "," & "98" & "," & "99" & "," & "100" & "," & "101" & "," & "102" & "," & "103" & "," & "104" & "," & "105" & "," & "106" & "," & "107" & "," & "108" & "," & "109" & "," & "110" & "," & "111" & "," & "112" & "," & "113" & "," & "114" & "," & "115" & "," & "116" & "," & "117" & "," & "118" & "," & "119" & "," & "120" & "," & "121" & "," & "122" & "," & "123" & "," & "124" & "," & "125" & "," & "126" & "," & "127" & "," & "128" & "," & "129" & "," & "130" & "," & "131" & "," & "132" & "," & "133" & "," & "134" & "," & "135" & "," & "136" & "," & "137" & "," & "138" & "," & "139" & "," & "140" & "," & "141" & "," & "142" & "," & "143" & "," & "144" & "," & "145" & "," & "146" & "," & "147" & "," & "148" & "," & "149" & "," & "150" & "," & "151" & "," & "152" _
 & "," & "153" & "," & "154" & "," & "155" & "," & "156" & "," & "157" & "," & "158" & "," & "159" & "," & "160" & "," & "161" & "," & "162" & "," & "163" & "," & "164" & "," & "165" & "," & "166" & "," & "167" & "," & "168" & "," & "169" & "," & "170" & "," & "171" & "," & "172" & "," & "173" & "," & "174" & "," & "175" & "," & "176" & "," & "177" & "," & "178" & "," & "179" & "," & "180" & "," & "181" & "," & "182" & "," & "183" & "," & "184" & "," & "185" & "," & "186" & "," & "187" & "," & "188" & "," & "189" & "," & "190" & "," & "191" & "," & "192" & "," & "193" & "," & "194" & "," & "195" & "," & "196" & "," & "197" & "," & "198" & "," & "199" & "," & "200" & "," & "201" & "," & "202" & "," & "203" & "," & "204" & "," & "205" & "," & "206" & "," & "207" & "," & "208" & "," & "209" & "," & "210" & "," & "211" & "," & "212" & "," & "213" & "," & "214" & "," & "215" & "," & "216" & "," & "217" & "," & "218" & "," & "219" & "," & "220" & "," & "221" & "," & "222" & "," & "223" & "," & "224" _
 & "," & "225" & "," & "226" & "," & "227" & "," & "228" & "," & "229" & "," & "230" & "," & "231" & "," & "232" & "," & "233" & "," & "234" & "," & "235" & "," & "236" & "," & "237" & "," & "238" & "," & "239" & "," & "240" & "," & "241" & "," & "242" & "," & "243" & "," & "244" & "," & "245" & "," & "246" & "," & "247" & "," & "248" & "," & "249" & "," & "250" & "," & "251" & "," & "252" & "," & "253" & "," & "254" & "," & "255" & "," & "256" & "," & "257" & "," & "258" & "," & "259" & "," & "260" & "," & "261" & "," & "262" & "," & "263" & "," & "264" & "," & "265" & "," & "266" & "," & "267" & "," & "268" & "," & "269" & "," & "270" & "," & "271" & "," & "272" & "," & "273" & "," & "274" & "," & "275" & "," & "276"


strNewContents = strFirstLine & vbCrLf & strContents
 
Set objFile = objFSO.OpenTextFile(strPath, ForWriting)
objFile.WriteLine strNewContents
 
objFile.Close



  DoCmd.RunSQL "DELETE * from T_TUG"
      
  MsgBox "作成完了", vbOKOnly
  
  DoCmd.SetWarnings True
  
  
End Sub
```


accessを使って256列以上のデータをCSVにエクスポートしたい

Accessでツールを開発する時の、データ抽出,更新,挿入,削除などの処理をする時の、SQL文とクエリの使い分けについて、開発に携わっている方がどうされているのか教えてください。

SQL文を自分で作成してもクエリデザインで作成しても同じ結果を得られると思いますが、皆さんはどちらを使用して開発をされていますでしょうか。
※例えば特定のテーブルのデータを抽出する時、
　VBAのコード内にSQL文(sql = "SELECT * FROM tbl_emp;")を作成し、それを実行してデータを取得する方法と、
　Access内のクエリデザインなどであらかじめ作成しておいたクエリをコード内で実行しデータを取得する方法があると思います

トランザクションが必要な処理はクエリを使用しないのはわかります。
もしトランザクションが必要で、その部分の処理SQL文で作成した場合、
そのほかの処理もクエリを使用せずに作成してしまった方が、後でソースコードを読み返す時、モジュールとナビゲーションウインドウのクエリを行ったり来たりせずに済むのではないかと思ってみたり、
はたまた、クエリを使用して開発して方が早いし楽なのかなと思ってみたりしています。
また、SQL文とクエリが混在するのにも若干の違和感があったりするのですが、そこは気にしなくてもよいのでしょうか。

みなさんは、どのようにされていますか？
また、使い分けの明確な基準はお持ちですか？
クエリしか使用しないまたは、SQLしか使用しないという方はいらっしゃいますか？


AccessVBAで開発に携わっている方、SQLとクエリの使い分けについて意見をお聞きしたいです

###前提・実現したいこと
VBAでシステムを作っているのですが、メインのプロシージャがあり、その中でCALLでプロシージャを呼び出しているのですが、呼び出したプロシージャでエラーが発生した時に、メインのプロシージャでエラー番号を受け取りエラー処理はできないでしょうか。

###発生している問題・エラーメッセージ

```
エラーメッセージ
```

###該当のソースコード
```ここに言語を入力
sub main_procedure()

on error goto エラー処理

call sub_procedure

exit sub

エラー処理:
Select Case Err.Number
Case ○○
msgbox "○○○"
case ○○
msgbox "○○○"
End Select
end sub

sub sub_procedure
ここで発生したエラーをmainで取得して処理したい
end sub

```

###試したこと
課題に対してアプローチしたことを記載してください

###補足情報(言語/FW/ツール等のバージョンなど)
より詳細な情報で

エクセルVBA CALLで呼び出したプロシージャ内でのエラー処理

初歩的な質問と思いますがよろしくお願いいたします。

サブフォームを含むフォームがあります。
メインフォームにある「クリアボタン」をクリックで
入力した内容をすべてクリアしたいのですが
サブフォーム部分のクリアの仕方がわかりません。

Private Sub コマンド29_Click()

Response = MsgBox("画面をクリアしますか。", _
       vbYesNo + vbQuestion + vbDefaultButton1, "受注票")
    If Response <> vbYes Then
        DoCmd.CancelEvent
        SendKeys "{ESC}", False
        Exit Sub
    End If

  Dim ctl As Control
  
  On Error Resume Next

  For Each ctl In Me.Controls
    With ctl
      If .ControlType = acTextBox Or acComboBox Then
         .Value = ""
      End If
    End With
  Next ctl
End Sub

現在ここまではできていてメインフォームはすべてクリアになります。
サブフォーム（コンボボックス1,2,テキストボックス）も
クリアしたい場合どのようにしたらいいでしょうか？

windows10、access2019

追記
 １（メイン）対多（サブ）の連結フォームです。

Access　サブフォームに入力した内容をクリアしたい

### 前提・実現したいこと
・サイトAをブラウザXで開くショートカットを作成するスクリプト
・AとXの部分を書き換えれば他のサイト、他のブラウザでも同様に作成できるもの
###
Wshを利用してそれぞれのショートカットを作成することはできました。
・特定のブラウザの起動
・特定のサイトの表示（規定のブラウザで開く）

しかし特定のサイトを特定のブラウザで開くショートカットが作成できません。
例えばGUIからショートカットを作成して以下を指定するとChromeでGoogleを開くものが作成できます。
"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" https://www.google.co.jp/

このようなものをスクリプトで作成したいのですがどの様にすればよろしいのでしょうか

特定のブラウザで特定のURLを開くショートカットを作成するスクリプト

```ACCESS SQL
SELECT コード, 項目名, 金額
FROM (SELECT * FROM 支給項目一覧) file 
UNPIVOT (金額 FOR 項目名 IN (手当1, 手当2) 
) AS unpvt
ORDER BY unpvt コード desc
;
```

上記SQL文をACCESSにて作りましたが、「FROM 句の構文エラーです。」とエラーメッセージが表示されてしまいます。

やりたいことは
横持のデータを
コード　手当1 手当2 手当3・・・・・・・
10001   100   300   400
10002   140   200   150
・
・
・

縦持ちにすることです
コード 項目名　金額
10001  手当1  100
10001  手当2  300
10001  手当3  400
・
・
・

UNPIVOTを使うとそのようにできるとわかりましたが、初歩的なエラーで躓いております。
どなたかご教授いただけますと幸いです。

よろしくお願いいたします。

Access SQlエラー（FROM句の構文エラーです）

実現したいこと
・Accessのクエリで抽出したデータをエクセルファイルに出力したい。

困っていること
・Access VBAでクエリからデータをエクセルファイルに出力するマクロを書いたがエラーが出て困っている。エラーが出る箇所はDoCmd.TransferSpreadsheet

```
エラーメッセージ：実行時エラー3191　複数回フィールドを定義することはできません。
```

### 該当のソースコード

```vba
Private Sub excel()
Dim str_file_name As String

str_file_name "D:\Users\○○○○\Desktop\出力結果.xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, Q_クエリ名, str_file_name, True

MsgBod "完了"
End Sub

```

Accessのバージョンは2016

ご教授頂けますと幸いです。


Access DoCmd.TransferSpreadsheet でエラーがでる

## テーマ、知りたいこと
氏名フィールドのレコードを、姓と名の間に全角スペースが1つ入った形に統一したいが、どのように実現するか。
## 背景、状況
全角スペース1つではなく半角スペースが2つ入っていたり、名の後ろにスペースが入っていたり、そもそもスペースが入っていなかったり…と、客先から預かったExcelデータの形がバラバラで困っています。
毎回Excel上やインポートしたAccess上で置換したり手動でスペースを入れたりしてデータを整形しているのですが、レコード数が多いとそれだけでかなりの時間を要します。
最短かつ確実にデータ整形するのに何か良い方法はありませんでしょうか？

全角スペース2つを全角スペース1つに置換、クエリでtrimした後、姓と名の間にスペースが無いものを手動でレコードを確認しながら手打ちしています。